Microsoft Office 2010 Key Repair numbers formatted

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,Buy Microsoft Office 2010, 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!),Windows 7 Home Premium, 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,Cheap Windows 7 Key, 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,Microsoft Office 2010 Key, like this:
=TRIM(CLEAN(A2))
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,Cheap Windows 7 Key, 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!
--Gary Willoughby <div