convert web copied text to numbers
 

I copied a website and pasted into excel. I need a formula to convert the
text to numbers.
Here are some examples, they contain leading 0"s, trailing 0's, "$" "," and
"."
$9,405.98
$9,457.19

$14,420.92
$5,077.31

Thanks for any help
--
Jim C

convert web copied text to numbers
 

If the text is in A1, then put this in B1:
=VALUE(A1)

"Jim C" wrote in message
...
I copied a website and pasted into excel. I need a formula to convert the
text to numbers.
Here are some examples, they contain leading 0"s, trailing 0's, "$" ","
and
"."
$9,405.98
$9,457.19

$14,420.92
$5,077.31

Thanks for any help
--
Jim C

convert web copied text to numbers
 

If he copied from the web I suspect he might have invisible html characters
(CHAR(160)
Then VALUE won't work, I would use Dave McRitchie's trimall macro found here

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


will remove all extra characters including CHAR(160)

using a formula this might work


=--TRIM(SUBSTITUTE(A1,CHAR(160),""))


--
Regards,

Peo Sjoblom




"Zone" wrote in message
...
If the text is in A1, then put this in B1:
=VALUE(A1)

"Jim C" wrote in message
...
I copied a website and pasted into excel. I need a formula to convert
the
text to numbers.
Here are some examples, they contain leading 0"s, trailing 0's, "$" ","
and
"."
$9,405.98
$9,457.19

$14,420.92
$5,077.31

Thanks for any help
--
Jim C

convert web copied text to numbers
 

The formula worked great: =--TRIM(SUBSTITUTE(A1,CHAR(160),""))
Thank you.
--
Jim C


"Peo Sjoblom" wrote:

If he copied from the web I suspect he might have invisible html characters
(CHAR(160)
Then VALUE won't work, I would use Dave McRitchie's trimall macro found here

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


will remove all extra characters including CHAR(160)

using a formula this might work


=--TRIM(SUBSTITUTE(A1,CHAR(160),""))


--
Regards,

Peo Sjoblom




"Zone" wrote in message
...
If the text is in A1, then put this in B1:
=VALUE(A1)

"Jim C" wrote in message
...
I copied a website and pasted into excel. I need a formula to convert
the
text to numbers.
Here are some examples, they contain leading 0"s, trailing 0's, "$" ","
and
"."
$9,405.98
$9,457.19

$14,420.92
$5,077.31

Thanks for any help
--
Jim C