Today’s blog post on repairing numbers that are formatted as text is brought to you by Gary Willoughby, who writes Help content for Microsoft Excel.
A few months ago, my new manager e-mailed us (that is, her team of Excel writers). She had a spreadsheet with some columns of numbers that she wanted to total, and the answer was resolving to 0. When you have lots of values in a column and their total is 0, you have a problem: Those numbers may not be numbers at all! (cue the scary music).
I love a challenge. She needed an answer, and fast. Turn me loose!
After a little inspection by using the LEN function to determine the length of some random cells (I don’t very often trust imported data!), I noticed that there were leading and trailing spaces and possibly non-printing characters in the cells. Because they all seemed to be between 10000 and 99999 and included commas, I knew every cell should contain 6 characters. LEN showed that many contained more than 6.
Apparently this data had been sent around in e-mail messages, maybe pasted into and copied back out of Word, batted around in who knows what other programs, and finally (after plenty of mayhem had been applied) pasted into a spreadsheet.
The first thing I did was use the CLEAN function nested inside the TRIM function, like this:
I put this formula in B2, and then dragged it down the B column for every row that contained a value in column A. The CLEAN function removed any non-printing characters, and TRIM finished up by stripping any remaining leading and trailing spaces.
Next, I selected the cells in column B that contained the formula and copied and pasted them onto themselves with the Paste Values command to convert the formulas to actual values. Then, I moved the original values out of column A and replaced them with the cleaned and trimmed values from column B. I saw a green indicator in the upper-left corner of each cell telling me these values appeared to be numbers formatted as text. Almost home!
Making sure all the cells containing values in column A were selected, I clicked the green indicator in cell A2 and then chose the Convert to Number option. Because I selected all the cells before I clicked Convert to Number, Excel acted on all selected cells that had that issue.
There…now I’m able to quickly add those numbers and get a real answer!