Numbers copied from text, convert back to numbers?

vegasbound365

New Member
Joined
Jul 7, 2004
Messages
41
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?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

New Member
Joined
Jul 7, 2004
Messages
41
did not work

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

vegasbound365

New Member
Joined
Jul 7, 2004
Messages
41

ADVERTISEMENT

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? :cry:
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,083
Messages
5,570,122
Members
412,305
Latest member
Mozz
Top