I frequently encounter Excel Worksheets with Smart Tags scattered around wanting very badly to share their ‘Number Stored as Text’ warning. Sometimes this warning is completely incorrect – the cell value really is a string even though it looks like a number (UPCs and US Zip Codes come to mind); sometimes the cell value really should be should be a number…
The problem with numeric values identified as ‘Numbers Stored as Text’ is that they can cause trouble with formulas, sorting and PivotTables – a broad enough range of activities/items to cause problems for just about anyone using the data.
Unfortunately simply changing the number format or alignment of the cells involved does not solve the problem. I like the information in the links below – they cover most of the helpful answers/suggestions that I have seen:
Daily Dose of Excel – Number Stored as Text (the post AND comments are good reading)
Jim’s Help Pages – Problems with imported data (also very good reading: Excel KB articles)
Notes On Solutions:
[please see the comments below from Gary Bouwman for some interesting information about these solutions and working in other languages]
‘Number Stored as Text’ Smart Tag Menu -
This works, but there are often better solutions. If you only have a few cells to convert the context menu from the Smart Tag may work for you (and does not require any code) – but with large ranges the conversion process can be slower than some of the methods detailed below. (Selection hint: When selecting ranges that you want to convert via the Smart Tag Menu make sure that the Smart Tag comes up on the first cell you select. If the start of your selection is a ‘normal’ cell the Smart Tag will not appear, even if your final selection includes cells that trigger the Smart Tag) (Note: Smart Tags will not appear in older versions of Excel!)
Formulas -
The links above mention the use of formulas – I think that Paste Special is usually a better option. Paste Special does not require you to find room for an extra row/column for your formulas – and (depending on your needs) does not require extra effort/key strokes to get the final version of the data into the correct position on the sheet or converted from formulas into values.
Paste Special -
The links above detail using paste special – a very good solution! Paste Special is available directly in the UI and is quite fast and easy – in code it can also be a good solution. The Daily Dose of Excel article specifically recommends the combination of ‘Copy Blank Cell/Paste Special/Add’. The ‘Copy Blank Cell/Paste Special/Add’ combination usually is the best – ‘Copy Cell with Value of 1/Paste Special/Multiply’ is also effective but can convert blank cells to zeros which is a problem in some data. (Side Note: In code I dislike needing to find a blank cell to copy before the paste special, but I have NEVER worked with a sheet that has every cell filled so it would not be hard to find a blank cell – this is purely a matter of taste…)
cell.Value = CDbl(Cell.Value) -
This style of coding (which could be any number of conversions such as CInt) does the job – but I have found it to be slow with large amounts of data.
[Range].Value = [Range].Value -
This solution is simple, fast and usually a very good option. I love the simplicity of this code – unfortunately it does not work on one of the reports I frequently use. I have not read about other people having failures – but for me [Range].Value = [Range].Value fails consistently on data I need to use. Because of the problems I have had I tend to use TextToColumns (which I have not (yet) seen fail).
TextToColumns -
This is an interesting method that runs quite quickly. TextToColumns works on a single column at a time and is a decent solution both from code and through the UI. The heart of the vb.net code that I use is below. This code is much more complex than [Range].Value = [Range].Value and the range that can be used is limited to a continuous selection in a single column – but for me TextToColumns has proven to be more robust than [Range].Value = [Range].Value, faster than [CellRange].Value = CDbl([CellRange].Value) and convenient since I am usually dealing with entire columns of a table.
This code block needs two variables defined: rangeToConvert (an Excel.Range that must be a continuous selection in a single column) and Delimiter (String).
Dim foundPreExistingDelimiter As Excel.Range = _
rangeToConvert.Find(What:=Delimiter, _
After:=rangeToConvert.Cells(1, 1), _
LookIn:=Excel.XlFindLookIn.xlValues, _
LookAt:=Excel.XlLookAt.xlPart, _
SearchOrder:=Excel.XlSearchOrder.xlByRows, _
SearchDirection:=Excel.XlSearchDirection.xlNext, _
MatchCase:=False)
If foundPreExistingDelimiter IsNot Nothing Then
Throw New System.ArgumentException( _
"Tab Delimiter used in the TextToColumns function " & _
"is found in the Range to Convert.")
End If
'The optional Fiedinfo:= is ommitted, I could not think of
'a use beyond the 'general' format since this is meant to
'eliminate Numbers Stored as Text rather than wrap
'TextToColumns()
rangeToConvert.TextToColumns(Destination:=rangeToConvert, _
DataType:=Excel.XlTextParsingType.xlDelimited, _
TextQualifier:=Excel.XlTextQualifier.xlTextQualifierNone, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, OtherChar:=Delimiter, _
TrailingMinusNumbers:=True)
TextToColumns could be a real mess if the parameters given to TextToColumns cause some of your cells to be split into multiple values (the main purpose of this function after all…) – I decided to deal with this potential problem by coding the delimiter character as a variable and checking to see if it exists in the rangeToConvert. If the delimiter is found an exception is thrown and the conversion is stopped (from a calling routine the exception makes it easy to wrap the conversion in a try-catch block and surround it with a For-Each loop that runs thru a list of possible delimiter characters).
Hope this was useful -
CM
Simple code to provide for range.value=range.value
Works very fast since it only takes the used cell for re-evaluation
Sub valueISvalue()
‘
‘ valueISvalue Macro
‘ Macro recorded 25-09-2006 by Erik Esveld
‘
‘
For Each mycell In ActiveSheet.UsedRange
mycell.Value = mycell.Value
Next
End Sub
erik – I like .UsedRange as well (although a quick search will reveal that many people have found it to be troublesome in some situations and use other methods to get the ‘used range’) – you should be able to simplify your code by just using:
Sub valueISvalue()
Activesheet.UsedRange.Value = _
Activesheet.UsedRange.Value
End Sub
[range].value = [range].value methods are quite fast – although (as I mentioned above) I do have one report (from a web interface) for which this method does not work.
CAM
I’ve tried several of the methods you refer to with some success. Your example for text-to-column is the only method that has worked for me so far. My particular issue is coding for use in a global environment. These simple methods all worked when my PC was set to US (English), but not when set to Germany (German) … until the text-to-column trick. Great workaround. I would never have thought to look at that as a fix.
Gary
OK. It was a great solution, but I’ve found a situation that does not work. When the PC region is set to French, texttocolumn converts the cell to a value as expected, with on exception. When the cell was already evaluated as numeric (no green triangles), it replaces the comma with a period for the decimal, and that cell is now text (with a green triangle).
What bugs me is this texttoolumn solution works well for other language regions.
Gary – really interesting comment about the French, I have not seen that mentioned before and that it would have bugged me too to find out about the , to . switch. If you find a work around let me know, I will have to experiment more with other languages…
Charles
I am programatically creating a csv file for export to other systems. Some things that need to be exported are defined as character and can be up to 50 characters long. If a user happens to fill those fields with numerics, Excel converts them to numeric representation such as 1.71717171717171E+29. I can not expect my users to convert all of this before using the file. How can I keep this from happening?
I have tried the paste special using either add 0 or multipy 1. Doesn’t work.
Text to Columns doesn’t work.
And copy and paste special – valuse only doesn’t work.
I have no knowledge of macroes or vb.
Any suggestions?
I am using the Text to Columns method and it seems there is a bug. If the topmost row(s) is not populated and the entire column is selected, the pasted cells jump up a few rows – sometimes 4 rows, sometimes 2.
??????
This is how it’s done, for most normal situations (ie, not French users).
Public Sub ConvertNumbersStoredAsText(WSName$)
Application.EnableEvents = False ‘prevent triggering event macros
Dim iCell
With ActiveWorkbook.Sheets(WSName)
For Each iCell In .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
If iCell.Errors.Item(xlNumberAsText).Value Then
If (Left(iCell.Text, 1) “0″) Then iCell.Value = iCell.Value ‘skip cells with leading zeros
End If
Next
End With
Application.EnableEvents = True
End Sub
For some reason the ‘not equals’ operator was lost when my post was translated to HTML. The line containing ‘iCell.Value = iCell.Value’ should have the ‘not equals’ operator before the “0″ in the ‘if’ statement.
[...] #split {}#single {}#splitalign {margin-left: auto; margin-right: auto;}#singlealign {margin-left: auto; margin-right: auto;}.linkboxtext {line-height: 1.4em;}.linkboxcontainer {padding: 7px 7px 7px 7px;background-color:#eeeeee;border-color:#000000;border-width:0px; border-style:solid;}.linkboxdisplay {padding: 7px 7px 7px 7px;}.linkboxdisplay td {text-align: center;}.linkboxdisplay a:link {text-decoration: none;}.linkboxdisplay a:hover {text-decoration: underline;} function opensingledropdown() { document.getElementById('singletablelinks').style.display = ''; document.getElementById('singlemouse').style.display = 'none'; } function closesingledropdown() { document.getElementById('singletablelinks').style.display = 'none'; document.getElementById('singlemouse').style.display = ''; } Best Music of 2009 List.Excel – ‘Number Stored as Text’ [...]