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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,696
Messages
6,103,357
Members
447,860
Latest member
thuexetheothang

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
Back
Top