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:
(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.
Done and done.
Oh, Okay. Sounds easy enough. Except, wait, there are no error indicators.
They are; plus, I can’t sum them or anything else, so I know they are indeed formatted as text.
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!
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.
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.
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.
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 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.
