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