Converting Text to Numbers in Excel w/ Additional Spaces

[scottrichards7]

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.

 

[Bernard Liengme]

In A1 I type ' $2,000.000 with a space after last zero. I use the apostrophe
(') to make sure I had text. I copied a blank cell (Excel will treat this as
zero). Selected the cell with ' $2,000.000 and used Edit | Paste Special
with Add specified. Now the cell show 2000; I can format it to display
$2,000.00.

Note that having copied the blank cell, I could have selected a range to do
the Paste Special
Try this and tell us if it works with your data

 

[Tyro]

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

 

[Tyro]

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

Tyro

 

[Ron Rosenfeld]

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.

Try:

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

in a helper column.

Or you could do Find <space>
Replace with <nothing>
Find <alt-160> (hold down <alt> key while hitting 0160 **ON THE NUMERIC
KEYPAD** )
Replace with <nothing>
--ron