Get Rid of Blank Spaces In Front of Numbers In Excel Stored As Text

If you've ever pasted a table from an email into Excel, you've probably encountered annoying blank spaces in front of numbers that prevent you from doing math with the numbers.

Example Pictured Below in Column B:

A simple formula will get rid of blank spaces in front of the numbers so that you can add cells.

Formula to Remove Blank Spaces in Cell A1
=trim(SUBSTITUTE(A1,CHAR(160),char(32)))

The above formula deletes out the blank spaces in front of numbers. Unfortunately, the majority of emails are in HTML format. When you copy a table from an email in HTML format and paste it into Excel, the breaking space character is automatically added in front of any number.

How to Get Rid of Number Stored As Text

1. Copy and Paste Special Values after using the trim/substitute function above.
2. The numbers will now have the message "number stored as text."

3. Highlight the column with the numbers. On the Data tab at the top of Excel's Ribbon, Select "Text to Columns" in order to fix the number stored as text message. (Pictured Below.)

4. Press Next. (Pictured Below.)

5. Check Other only. Press Finish. (Pictured Below.)

6. You can now do math with the cells because the number stored as text issue is fixed:

If you want to learn how to do this in VBA, watch the below video from my Youtube Channel. I recommend watching it in 1080P HD by selecting the Gear Icon for best quality. My Youtube channel has the code used in the video in the video description.

If you are on mobile, then please watch the video directly on My Youtube for clearest quality.