Converting Text to Numbers in Excel w/ Additional Spaces

S

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.
 
B

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
 
T

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top