Number to Text Error

[Jaye7]

Sometimes I receive excel files where various columns should have been stored as text when they converted them to excel and if it is only a couple of cells that are incorrect then I click on the cell and then formula bar and type an ' at the start of the number to change it to text i.e 7000 ends up as '7000.

I have tried formatting the cells to do this but nothing happens.

On large files I save them as text and then reopen them changing the specific columns to text, however I would prefer to have a script that I can run based on whatever column I am in, it would change that column to the right format.

Thanks.

 

[JoeMo]

Will this do it?

Sub NumsToTex()
Dim rng As Range
On Error Resume Next
With ActiveCell.EntireColumn
    Set rng = .SpecialCells(xlCellTypeConstants, 1)
    If Not rng Is Nothing Then
        On Error GoTo 0
        For Each c In rng
            c.Value = "'" & c.Value
        Next c
   End If
End With
End Sub

 

[Jaye7]

That's Perfect, thanks very much JoeMo, much appreciated.