Convert Badly-Formatted Text In Excel Back Into Numbers

Convert Badly-Formatted Text In Excel Back Into Numbers

Excel is a whizz at totalling numbers, but if too much copy-and-pasting and editing has turned some numbers in your spreadsheet into text format, then the results are often way out of whack. It’s almost impossible to fix that kind of problem without some manual intervention, but the TRIM and CLEAN functions can make the task simpler.

The Microsoft Office Blog details how a combination of the TRIM, CLEAN and ‘Convert to Number’ functions in Excel can take numbers that are surrounded by extraneous data and make them usable again. Getting badly-formatted spreadsheets isn’t uncommon, so it’s good to know about these options even if you’ll still need to double-check the results afterwards. Hit the link below for the full process, and share your own spreadsheet cleanup tips in the comments.

Repairing numbers formatted as text [Microsoft Office Blog]


  • I find it’s the opposite with the new Microsoft Office 2010. I am constantly frustrated with Excel trying to be smart and converting things like 20-4 to 20th April.

    P.S. 20-4 is my lecture hall.

    • Typing 20-4 into a cell in Excel has always been interpreted as 20 April (don’t blame Excel 2010!).
      Preceeding a number by a single quote will store the number as numeric text, so you could type ’20-4 each time.
      In your particular case, perhaps the neatest solution is to set up an AutoCorrect Option (Excel Options … Proofing tab) so that 20-4 is automatically changed to ’20-4. It will then not be changed into a date when entered.

Show more comments

Log in to comment on this story!