Converting Text to Numbers in Excel w/ Additional Spaces
 

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.

Converting Text to Numbers in Excel w/ Additional Spaces
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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.

Converting Text to Numbers in Excel w/ Additional Spaces
 

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.

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.

Converting Text to Numbers in Excel w/ Additional Spaces
 

On Wed, 21 Jan 2009 18:15:00 -0800 (PST), wrote:

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