View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Tyro[_3_] Tyro[_3_] is offline
external usenet poster
 
Posts: 6
Default Converting Text to Numbers in Excel w/ Additional Spaces

And you can play with either B1 or C1 with paste special values or whatever

Tyro

"Tyro" wrote in message
...
Have you tried the value function? If a1 is: $1,234 with a leading and
trailing space then if B1 is: =value(a1) then b1 = 1234 and if C1 is: =B1
then C1 will have the numeric equivalent of a1

Tyro

wrote in message
...
I have a column of numbers that look like $2.000, $22.000, $222.000 or
$2,222.000. The problem is they have a leading space (before the $)
and lagging space (after the third 0).

I've tried text to columns, =trim, =clean and copy/past special/values
or addition or multiple with no luck!

I just need to convert them text to numbers - HELP! Solutions are much
appreciated.