Do You Hate The Numbers Stored As Text Error?


How many times have you encountered the “Numbers Stored as text” error in your data sets? It interferes with your lookups 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:

=IFERROR(A1*1,TRIM(A1))

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

'######################################################################
'AutoTrim  : This Sub accepts a Range as an argument and Auto Trims the
'cell values. It trims strings, converts Dates and Numbers to the
'appropriate format.
'######################################################################
'Author     : Ejaz Ahmed
'Email      : StrugglingToExcel@outlook.com
'Date       : 11 August 2014
'Website    : https://strugglingtoexcel.wordpress.com/
'######################################################################
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))
                Else
                    '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))
                    Else
                        '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
    Else
        'Deal with Single Cells separately
        If IsDate(TempArray) Then
            TempArray = CDate(TempArray)
        Else
            If IsNumeric(TempArray) Then
                TempArray = CDbl(TempArray)
            Else
                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

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.

'######################################################################
'RangeHasFormulas  : Function that accepts a Range Object as an argument
'and determines if any of the cells have formulas. The HasFormula
'property of a Range Object Returns False if all the cells do not have
'formulas; returns True if all the cells have formulas; and returns
'Null if some have formulas and some don't.
'######################################################################
'Author     : Ejaz Ahmed
'Email      : StrugglingToExcel@outlook.com
'Date       : 11 August 2014
'Website    : https://strugglingtoexcel.wordpress.com/
'######################################################################
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
Else
    If TempVar = True Then
        'If True, all cells have Formulas
        RangeHasFormulas = True
    Else
        'If False, none of the cells have formulas
        RangeHasFormulas = False
    End If
End If

End Function

All you need to do now is select your entire Database, just run this macro, sit back and relax. Download Auto Trim.xlsm from Dropbox and 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.

Update:

  • 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.
Advertisements

13 thoughts on “Do You Hate The Numbers Stored As Text Error?

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

      There are also other advantages.

      Like

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

    Like

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

      Like

    • 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:
      http://www.bettersolutions.com/vba/vnn529/LT622611112.htm

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

      Like

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

    H

    Liked by 1 person

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

      Like

    • 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

      • 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

        • 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

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s