How to deal with the Numbers Stored As Text Error in Excel?

How many times have you encountered the “Numbers Stored as text” error in your data sets? It interferes with your LOOKUP and MATCH functions, and arithmetic calculations. Excel has a Convert to Number functionality to help with this situation, but it could be a lot better. You have to deal with your columns one at a time, sometimes one cell at a time. Also, I noticed that if the dataset is huge, excel takes a lot of time to push through; occasionally, it is so slow that you can see the cells getting updated one by one.

Convert To Numbers

A quick way to deal with this is to create a temporary column and use the Value function. You may get #Value errors if the string does not represent a numeric value. I use the following formula to do the trick:


This converts all the numbers to “numbers”, trims the text and takes care of the #Value errors if any. You can Copy-Paste-Special-Values over the existing column, and remove the temporary column if you want. This technique works if you have only the Unique Identifier column interfering with your lookups. Imagine your entire database imported into excel  as text (with that annoying singe quote in front), you’d be surprised how often that happens. I bet you would not have the patience to set up temporary columns for every field, would you?

I ran into this problem very often, so I buckled down and started thinking of ways to automatically clean up the columns. My first instinct was to deal with the problem one cell at a time. I created a user defined function that cleans up one cell, and then ran a For Each loop through all the cells in a range. This method was extremely slow, even slower than the built-in Convert To Number function. Then I figured using Arrays would speed things up immensely.  And that is when I realized something amazing; a single line solved this entire problem:

Selection.Value = Selection.Value

This got the job done in a flash.  It automatically got rid all the Convert To Number errors. Who knew excel was already programmed to automatically do this right? If only Microsoft thought to give users the ability to call it at will. However, note that this does not trim cells, and it does not handle dates well. Also, this wont work if you set the Cell’s Number Format to Text.

I ironed out the issues and wrote a sub that trims string, handles dates well and changes Case (Lower/Upper/Proper if you need it to).

Sub AutoTrim(Optional ByRef WhichRange As Range, _
    Optional ByVal ChangeCase As Boolean = False, _
    Optional ByVal ChangeCaseOption As VbStrConv = vbProperCase)

'Declare Sub Level Variables and Objects
Dim MessageAnswer As VbMsgBoxResult
Dim EachRange As Range
Dim TempArray As Variant
Dim RowCounter As Long
Dim ColCounter As Long

'Use the Active Selection if the user did not pass a Range Object to the
'WhichRange Argument
If WhichRange Is Nothing Then
    Set WhichRange = Application.Selection
End If

'If the Range has formulas, it will be converted into values
'Therefore, ask user for permission to proceed
If RangeHasFormulas(WhichRange) Then
    MessageAnswer = MsgBox("Some of the cells contain formulas. " _
        & "Would you like to proceed?", _
        vbQuestion + vbYesNo, "Struggling To Excel")
    If MessageAnswer = vbNo Then Exit Sub
End If

'Loop through each area, So we can loop through all the _
'rectangular boxes
For Each EachRange In WhichRange.Areas
    TempArray = EachRange.Value2
    'If Each range were a single cell, then EachRange.Value
    'would not be an array. Consequently, we have to deal with both
    'these situations separately
    If IsArray(TempArray) Then
        For RowCounter = LBound(TempArray, 1) To UBound(TempArray, 1)
            For ColCounter = LBound(TempArray, 2) To UBound(TempArray, 2)
                'First Check if it is a date
                'Excel Confuses mm/dd/yyyy and dd/mm/yyyy when we just use the
                'Selection.Value = Selection.Value technique. Copy Paste Special
                'Multiply by 1, also suffers from this problem.
                If IsDate(TempArray(RowCounter, ColCounter)) Then
                    TempArray(RowCounter, ColCounter) = _
                        CDate(TempArray(RowCounter, ColCounter))
                    'Check if it is a number
                    If IsNumeric(TempArray(RowCounter, ColCounter)) Then
                        'Convert it into a Double Variable if it is a number
                        TempArray(RowCounter, ColCounter) = _
                            CDbl(TempArray(RowCounter, ColCounter))
                        'Otherwise it is Text. Trim it. I am using Excel's trim
                        'function because it clears double spaces also.
                        TempArray(RowCounter, ColCounter) = _
                            Application.Trim(TempArray(RowCounter, ColCounter))
                        'Finally, Change Case if the user wants to
                        If ChangeCase Then
                            TempArray(RowCounter, ColCounter) = StrConv( _
                            TempArray(RowCounter, ColCounter), ChangeCaseOption)
                        End If
                    End If
                End If
            Next ColCounter
        Next RowCounter
        'Deal with Single Cells separately
        If IsDate(TempArray) Then
            TempArray = CDate(TempArray)
            If IsNumeric(TempArray) Then
                TempArray = CDbl(TempArray)
                TempArray = Application.Trim(TempArray)
                If ChangeCase Then
                    TempArray = StrConv(TempArray, ChangeCaseOption)
                End If
            End If
        End If
    End If
    EachRange.Value2 = TempArray
Next EachRange

End Sub

All you need to do now is select your entire Database, just run this macro, sit back and relax.



  • Another popular solution to this problem is to just type “1” in any cell; copy it; and paste-special-value it with the Multiply operator. While this handles Numbers like a charm, it fails to convert dates properly.
  • I heard complaints that my code did not handle numbers with commas and decimal points. Consequently I amended the code.
  • The macro now uses the following logic:
    • First Check if the string is a date. If it is, apply the CDate function.
    • Else, check if it the string is Numeric, if It is, apply the CDbl function. This function takes care of the numbers with commas and decimal points.
    • If the string is neither a date, nor a number, it must be a string. Therefore, Trim it with Excel’s trim function. I chose to use Excel’s trim function, because it also removes double spaces.



Let me know how you like it. I added a bunch of cover macros in there that illustrates how this sub could be used. There is a sheet named JustValues that has a very crude sample data set. I have copy pasted it in the Test sheet for you to try the cover macros.

I have not tried this macro on a system that uses the m/d/yyyy date system, do let me know if it doesn’t work on your computer. Also if you have dates in the d/m/yy format, you would benefit from reading this article.


I had to write a small supporting function that determines if any of the cells in a range have formulas in it. You may need to use something like this in your applications.

Function RangeHasFormulas(ByRef WhichRange As Range) _
    As Boolean

'Declare Function Level Variables
Dim TempVar As Variant

'initialize Variables
TempVar = WhichRange.HasFormula

'Check if the Variant Variable is Null (This avoids an error)
If IsNull(TempVar) Then
    'If Null, some cells have Formulas
    RangeHasFormulas = True
    If TempVar = True Then
        'If True, all cells have Formulas
        RangeHasFormulas = True
        'If False, none of the cells have formulas
        RangeHasFormulas = False
    End If
End If

End Function

Published by Ejaz

I specialized in actuarial science in my masters’ program and I am determined to become a qualified actuary. I invest extra time and effort into creating intuitive spreadsheet applications that improve productivity at the workplace. I also enjoy spending my leisure time creating digital art; I publish my designs on my art in this blog named Designecdotes. Most of the people who have dramatically influenced my life were enthusiastic academicians. I believe that the greatest gift in life is having a nurturing mentor, and I would like to be that for someone. I owe it to my future mentees to be the best at what I do, and I consciously direct my endeavors at gathering what I consider essential traits of a seasoned mentor.

22 thoughts on “How to deal with the Numbers Stored As Text Error in Excel?

  1. Thank you very much for your investigation of this problem. These procedures look to be extremely useful.

    My usual method of dealing with numbers stored as text is to select the column and use the text-to-columns feature.


    Liked by 3 people

  2. I am glad you found it useful. This is the first time I am hearing about Text-To-Columns as a solution. Another VBA user suggested I check it out. Thanks for letting me know.

    Do you think this procedure has a significant advantage over the text to column method?

    I’d like to mention that as a solution in my post.


  3. Some functions work without the WorksheetFunctions property.

    The Difference I guess is that WorksheetFunction.WhicheverFunction generates an error message in VBA. So you would need to On Error…. Error handle it.

    And the Application.WhicheverFunction just returns an error value, if you assign it to a Variant Variable. You can use the isError() function to deal with it.

    There is a very vague reference to that behavior in this link:

    It talks about the the match function though. I always check if Application.WhateverFunction works, and If It does, I just roll with it.


  4. I’ve used the text-to-columns feature but for some reason it sometimes will not clean it very well for me. I have found numerous times that when using text-to-columns that my vlookup formulas will still not be able to lookup properly.

    Liked by 1 person

  5. Hi Chris, Text to Column fails sometimes because it does not Trim the text. Otherwise, it is the best way to deal with this situation without macros. Also, you have to apply it one column at a time, which can be tedious at times. It is way more easy to Select the entire table and let the macro do its thing.

    Liked by 1 person

  6. Yeah, I typically only used it for my ID column in my raw data so I could do my lookups but always had problems, so reverted to Convert Number Stored as Text. But I like your macro because I can just clean up my entire data set at once and it just makes everything more legible!

    Liked by 1 person

  7. It is madness for MS to have Excel import numbers as text. The core purpose of a spreadsheet is to make manipulation of numbers easier. Fixing this with alot of manual busywork is also not a solution. When dealing with lots of data, it needs to be configurable, so it is automatic.


  8. I agree these functionalities should be part of excel. However, we also have to acknowledge that people may need some of their data to remain in text format.

    For instance, I have worked with data sets that generate Policy Numbers using a certain convention, and the number may have to start with zeros. And the user may not be happy about Excel to converting that to a number.

    Excel does not have a built in set of tools to clean up data. That needs to be addressed though.


  9. Glad you liked it. It is the fastest way to “copy paste”, because it does not use the clipboard.

    There are also other advantages.


  10. Hi Ejaz,
    I can’t download Auto Trim file. Could you please fix Drop Box download link?
    Thank you for your post.

    Kind Regards



  11. Sorry for the inconvenience. I am converting them to one drive links for the time being, while I workout a better solution.


  12. Hello, I copied and pasted this in to a workbook as well as my personal workbook but do not see it on the macro list. I downloaded the your file from onedrive and again, I can see the macro in VBA editor but not available as an option to run in the macro window. I’m sure I’m overlooking something simple here?


  13. Hi thank you for showing interest in this piece of code. Yes, this macro takes a range as an input (argument) and as such cannot be called directly from the macro list; and Sub that takes an input will not show up in that list.

    You have to write a cover macro to call this sub.

    Sub FixSelection()
    Call AutoTrim(Selection)
    End Sub


  14. Hi there thank you for your efforts. In my case i dont need cell to get to date. My original file has cell data as 1.60 and when i use replace to turn “.” into “,” then i get the error and i have to deal with it to get cell values into “Numbers”. Using your macro if cell was 1,6 with error when macro run it gives me 43617.
    Is there anything i can alter to your code to fix this?


If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: