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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,990
Messages
5,834,761
Members
430,318
Latest member
ZackChua95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top