Repair numbers formatted as text


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).

Numbers formatted as text.
 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.

Because the LEN function shows 17 characters, the cell needs cleaning up.
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:

After cleaning, the values in the B column look better than the A column
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!The indicator suggests that this is a number formatted as text

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.

Choosing the Convert to Number option from the menu.
There…now I’m able to quickly add those numbers and get a real answer!
Because they are now right-aligned, these are numbers, and the SUM command works

–Gary Willoughby