Numbers copied from text, convert back to numbers?

[vegasbound365]

I pasted some data, including dollar amounts, from an e-mail into Excel 2003. After I performed a text to column function, the dollar amounts are still treated as text and I cannot subtotal anything.

I tried to change the format of the cells, but that doesn't work.

I went into Tools/Options/Error Checking/and made sure the "text to numbers box" is checked and the "enable background error checking box" is checked, but still nothing.

Any advice?

 

[Andrew Poulsom]

Try Edit|Replace $ with nothing.

 

[nbrcrunch]

1. put a numeric 1 in a cell
2. Copy that value
3. Select the range of numbers
4. Edit / Paste Special -- Multiply. click ok

 

[vegasbound365]

did not work

Andrew and nbr:
I tried both attempts and still nothing. Boy, this is frustrating!

 

[vegasbound365]

I see what's wrong

I notice that in each cell, there are multiple spaces before and after the data that I need to format as numbers.

After I delete all of the spaces, THEN I can format as numbers. This is not the most efficient way of doing this, since I have a few HUNDRED lines.

Is there an easier way?

 

[nbrcrunch]

yes, use the trim function to remove those spaces...

1. in a different column use: =TRIM(MyNumber)
where "mynumber" is the cell reference containing the padded number-txt.

2. In the cell that you just wrote the formula, double-click the grab-handle to copy all the way down to the last record.

3. Copy the whole new column and with it still select, paste-special, values only (right on top of itself).

4. With the column still selected, Copy, then select the old column and paste.

 

[vegasbound365]

Thanks!

Thank you, that worked great!

As your signature states, teach a man to fish. . .