Help with Excel number conversion?

[Wheelz]

I'm working on a project at work in which I will be receiving stats from a third party via e-mail. That is, the information is in the actual body of the e-mail, not an attachment. I've asked if the info can be sent via spreadsheet, but have been told no, this is what I'm getting. It looks something like this: ....Transaction #..... Amount($) ......QUANTITY ......... TRANS_DATE
1 ......... 300430 ......... 0.4 ......... 15.527000 ......... 2012-02-17 9:50:46 AM
2 ......... 300456 ......... 0.09 ......... 11.901000 ......... 2012-02-1 7 10:38:01 AM
3 ......... 300738 ......... 0.03 ......... 15.249000 ......... 2012-02-17 5:53:51 PM
4 ......... 300754 ......... 0.02 ......... 7.757000 ......... 2012-02-17 6:36:44 PM
5 ......... 300783 ......... 0.01 ......... 6.806000 ......... 2012-02-17 7:47:26 PM
6 ......... 300668 ......... 0.02 ......... 10.350000 ......... 2012-02-17 4:19:51 PM(The dots are not there. I added them to separate the columns for this example.) What I need to do is multiply the dollar amount by the quantity and total the whole thing. I copy and paste the table into a spreadsheet, and I know how to separate it into columns. A couple of quick and easy formulas and I should be good to go, right? Except the numbers aren't numbers, they're text. OK, I know there's a way to convert text into numbers, I'll just use "Help" to find it. On the Tools menu, click Options.
On the Error Checking tab, make sure that the Enable background error checking check box and the Number stored as text check box are both selected, and then click OK.
Done and done. On the worksheet, select any single cell or range of adjacent cells that has an error indicator in the upper-left corner.
Next to the selected cell or range of cells, click the error button that appears , and then click Convert to Number.
Oh, Okay. Sounds easy enough. Except, wait, there are no error indicators. Numbers that are formatted as text are left-aligned instead of right-aligned in the cell.They are; plus, I can't sum them or anything else, so I know they are indeed formatted as text.With error checking turned on, they are also marked with an error indicator.Error checking is turned on. I have double, triple, and quadruple-checked this. But there is no error indicator. Therefore there is no error button and no handy-dandy "Convert to Number" option. I am officially stuck. What am I missing? Help, please!

[fubbleskag]

Highlight the area you want to convert. Click the "Number" box on the "Home" tab of the ribbon, if you're using the newer version of Office with the ribbon, or press Alt-O and then E to bring up the "Format Cells" dialog. Choose the "Number" tab, and then choose "Number" in the selection list on the left.

[Wheelz]

Highlight the area you want to convert. Click the "Number" box on the "Home" tab of the ribbon, if you're using the newer version of Office with the ribbon, or press Alt-O and then E to bring up the "Format Cells" dialog. Choose the "Number" tab, and then choose "Number" in the selection list on the left.One of the first things I tried. No go. :(

[Wheelz]

I've figured part of it out. I was capturing spaces along with the numbers, and deleting the spaces caused the cells to convert automatically. I guess spaces may as well be letters to Excel, and it can't do anything with a cell including both letters and numbers.

This is still problematic, though, since not every entry has the same number of characters, so I can't reliably cut all the spaces off when creating columns. There is still a bunch of manual cell-by-cell manipulation necessary, unless I can figure out a way to make Excel ignore the spaces in the first place.

[Baracus]

How are you importing the text?

If you are using the "Text Import Wizard" then choosing "delimited" rather than "fixed width" should make it so it doesn't matter how many spaces there are.

[Wheelz]

I'm just using copy (CTRL+C) and paste (CTRL+V). How do I get the Wizard?

[mcgato]

Make a new column and use the formula =value(trim(a2)).

[Baracus]

I'm just using copy (CTRL+C) and paste (CTRL+V). How do I get the Wizard?
Right-click. Select "Paste Special". When the dialog box pops up select "text". After your text is pasted there should be a little clipboard-y icon next to it. Left-click it and choose "use text import wizard".

I am not sure if there is a more direct way.

[Duke]

Make a new column and use the formula =value(trim(a2)).

This should do it--the =trim() function gets rid of spaces. I just checked it in Excel 2010 and it works correctly.

[Munch]

I usually just paste the column into a text file, do a "Find & Replace" for spaces to delete them, then input them back into the Excel file. That usually strips out any weird formating issues as well.

[beowulff]

This is a job from “GREP” - I assume you are one Windows? Try this: http://www.wingrep.com/

[Wheelz]

Right-click. Select "Paste Special". When the dialog box pops up select "text". After your text is pasted there should be a little clipboard-y icon next to it. Left-click it and choose "use text import wizard".

I am not sure if there is a more direct way.This did the trick like a charm!

Thanks for everyone's responses.