Converting text to number format

[Guest]

I have copied some data down from a web site first into a text file and then
uploaded this into Excel using the tab delimited converter. I cannot convert
the numbers in the colums onto real numbers. I have tried the following:

Excel functions:
Value
Text (thought about converting to text and then number)

I have also cut and paste special the data as "values"
Tried using a multiplication by 1 factor in paste special

Any further thoughts

Thanks

 

[Sandy Mann]

If you downloaded the *numbers* from the web then the probably have spaces
or non-breaking spaces [Char(160)] attached so try something like

=SUM(TRIM(SUBSTITUTE(G17,CHAR(160),"")))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

You're very welcome. Thanks for the feedback

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Top answer, thanks a lot Sandy

If you downloaded the *numbers* from the web then the probably have
spaces
or non-breaking spaces [Char(160)] attached so try something like

=SUM(TRIM(SUBSTITUTE(G17,CHAR(160),"")))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

I have copied some data down from a web site first into a text file and
then
uploaded this into Excel using the tab delimited converter. I cannot
convert
the numbers in the colums onto real numbers. I have tried the
following:

Excel functions:
Value
Text (thought about converting to text and then number)

I have also cut and paste special the data as "values"
Tried using a multiplication by 1 factor in paste special

Any further thoughts

Thanks

 

[Guest]

Yes...very good formulae. I read many posts performing macros and using paste
value. I just wanted to incorporate a formulae to automate everything so this
is an excellent formula for me. Thank you.

 

[Balan]

Mr.Sandy,
Your suggestion was helpful to me in converting numbers imported from the
web; but only partially. Numbers which were like 29,256.26 were converted.
However, numbers which were like 3,56,789.24 continue to remain same and I am
getting the sign #VALUE ! Kindly advise how this problem could be solved. I
know I am writing to you based on a suggestion given by you in 2007. I am
sorry for giving you the trouble.

You're very welcome. Thanks for the feedback

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Top answer, thanks a lot Sandy

If you downloaded the *numbers* from the web then the probably have
spaces
or non-breaking spaces [Char(160)] attached so try something like

=SUM(TRIM(SUBSTITUTE(G17,CHAR(160),"")))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


I have copied some data down from a web site first into a text file and
then
uploaded this into Excel using the tab delimited converter. I cannot
convert
the numbers in the colums onto real numbers. I have tried the
following:

Excel functions:
Value
Text (thought about converting to text and then number)

I have also cut and paste special the data as "values"
Tried using a multiplication by 1 factor in paste special

Any further thoughts

Thanks

 

[Bernard Liengme]

Try:
=--(TRIM(SUBSTITUTE(SUBSTITUTE(G17,CHAR(160),""),",","")))
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

Mr.Sandy,
Your suggestion was helpful to me in converting numbers imported from the
web; but only partially. Numbers which were like 29,256.26 were
converted.
However, numbers which were like 3,56,789.24 continue to remain same and I
am
getting the sign #VALUE ! Kindly advise how this problem could be solved.
I
know I am writing to you based on a suggestion given by you in 2007. I am
sorry for giving you the trouble.

You're very welcome. Thanks for the feedback

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Top answer, thanks a lot Sandy

:

If you downloaded the *numbers* from the web then the probably have
spaces
or non-breaking spaces [Char(160)] attached so try something like

=SUM(TRIM(SUBSTITUTE(G17,CHAR(160),"")))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


I have copied some data down from a web site first into a text file
and
then
uploaded this into Excel using the tab delimited converter. I
cannot
convert
the numbers in the colums onto real numbers. I have tried the
following:

Excel functions:
Value
Text (thought about converting to text and then number)

I have also cut and paste special the data as "values"
Tried using a multiplication by 1 factor in paste special

Any further thoughts

Thanks