Fix Excel Text Displayed As Scientific Number

VB Code fixes Excel text displayed as scientific notation or text stored with a leading character such as an apostrophe or space.

Sub GEN_FIX_SCN()
'Created 01/25/2008 by Ron Willoughby

Dim AWK_NME, CHK_NME, END_ROW, MSG1, TITLE1
Dim x As Double

    AWK_NME = ActiveWorkbook.Name
    MSG1 = "Currently, the Active Workbook is named: " + AWK_NME _
      + ".  Is this the Workbook with which you want to begin" _
      + "this program?  (Y/N)"
    TITLE1 = "CHECK WORKBOOK NAME"
    CHK_NME = UCase(InputBox(MSG1, TITLE1, "N", 5000, 5000))
    Select Case CHK_NME
     Case Is <> "Y"
      Exit Sub
    End Select
   
    Application.Goto Reference:="R2C1"  'First cell of the column to be fixed
    Selection.End(xlDown).Select        'Finds last row of selection
    'EMPTY CELLS IN THE COLUMN WILL NOT WORK!  FILL WITH A SINGLE SPACE.
    END_ROW = Selection.Row           'Defines END_ROW
    Application.Goto Reference:="R2C1:R" & Trim(END_ROW) & "C1"
       'Selects current region
    Selection.NumberFormat = "@"        'Formats region as text
    Application.Goto Reference:="R2C1"  'First cell of the column to be fixed
   
    For x = 1 To END_ROW
       If Selection = "" Then
         MsgBox "Done!"
         Range("A2").Select
         Exit Sub                        'Ends routine at empty cell
       End If
       Selection = Mid(Selection, 1, Len(Selection))
       Application.Goto Reference:="R[1]C1" 'Advances to next row in column
    Next x
   
    MsgBox "Done!"
    Range("A2").Select
      
End Sub

'This fixes any text displayed as a scientific number; if the data contains a
'leading apostrophe or space (or any other extra character), change line 18 to
'read:
'Selection = Mid(Selection, 2, Len(Selection)-1)