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:
=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).
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.
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.
Download
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.
Bonus
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
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
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.
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.
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.
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!
If you are using Excel’s Trim function instead of VBA’s wouldn’t the code need to read: Application.WorksheetFunction.Trim(TempArray) ?? Or are they one in the same?
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.
Ejaz, do you need to change the logic dealing with the single cell selection to match the multi-cell selection? (ie check for numerical value, etc….)
Nice Catch Chris! I totally forgot about it.
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.
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.
Selection.Value = Selection.Value
This line of code is Amazing..It helped me a lot 🙂
Glad you liked it. It is the fastest way to “copy paste”, because it does not use the clipboard.
There are also other advantages.
Hi Ejaz,
I can’t download Auto Trim file. Could you please fix Drop Box download link?
Thank you for your post.
Kind Regards
Bhavik
Sorry for the inconvenience. I am converting them to one drive links for the time being, while I workout a better solution.
Please try the link now.
Thank you m8, for your prompt response.
How did you like the onedrive download experience? Did you have to sign into a Microsoft account to access it?
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?
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
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?
I have a function that turns a cell value into what it needs to be. Just alter it as per your requirement.