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

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

"Jim C" <(E-Mail Removed)> wrote in message
news:E1900E88-CA9C-4D58-8C30-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If the text is in A1, then put this in B1:
> =VALUE(A1)
>
> "Jim C" <(E-Mail Removed)> wrote in message
> news:E1900E88-CA9C-4D58-8C30-(E-Mail Removed)...
>>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
>
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > If the text is in A1, then put this in B1:
> > =VALUE(A1)
> >
> > "Jim C" <(E-Mail Removed)> wrote in message
> > news:E1900E88-CA9C-4D58-8C30-(E-Mail Removed)...
> >>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
> >
> >
>
>
>