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
