Excel Displays Long Numbers in Scientific Notation
Excel can be a bit too ‘helpful’ at times and once such case is with long numbers. If you have a number in Excel longer than 14 digits, Excel will display the number in Scientific Notation. To ‘fix’ this, you can format the cells as Numeric with 0 decimal places, but, what if you need the cells to be formated as Text? Again Excel tries to be helpful and puts the Scientific Notation back in the cells. Not what is desired.
If you only have a few row of values, you can press F2 to enter edit mode and then press ENTER to exit edit mode and the text is formated correct. This, however, is not a desirable way when you have many rows of text which needs to be ‘fixed’.
To ‘fix’ many rows, use the Macro feature of Excel to convert the values to uppercase. Yes, I know it sounds strange to convert numbers to uppercase, but this works. To create the macro do the following (based on Excel 2003)…
On the Menu go to Tools -> Macro -> Visual Basic Editor
This will open a new application.
Right Click on the VBAProject(Book1) entry in the Left Pane. Where Book1 is the name of you Excel file, so the entry may be worded slightly different.
Select Insert -> Module
In the right side, you will notice two drop down lists at the top with the labels ‘(General)’ and ‘(Declarations)’. Copy the text below into the right side pane…
— Copy Text below this line —
Sub ConvertToUpperCase()
‘ Macro to convert text in cell to uppercase
‘ change the value in the ‘Range’ entry to the desired range of cells
Range(“A1:A2200”).Select
For Each cellObject In Selection
With cellObject
.Formula = StrConv(cellObject, vbUpperCase)
End With
Next
End Sub
— Do not copy This line —
Once the above is copied into the editor and the ‘range’ value (you can change this later if needed) is correct, close the editor. If prompted to save the changes select Yes.
To run the Macro, go to Tools -> Macro -> Macros on the menu. Select the ‘ConverToUpperCase’ macro and select Run
