puting an apostrophe before a number

[Mr Mayor]

hi

how do you format numbers to have an apostrophe before all the numbers

eg. 100 '100
7 '7

the are too many of the numbers and can not do it manually, looking for the formula

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow

If IsNumeric(.Cells(i, "A").Value) Then

.Cells(i, "A").Value = "'" & .Cells(i, "A").Value
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[Ken Puls]

Bob's route will actually put an apostrophe in the cell. If you want them to just look like they have an apostrophe, but still work like numbers, you can use a custom number format:


Select the range of cells
Right click and choose "Format Cells"
Go to the "Number" tab
Select "Custom"
Under "Type" enter the '0
Click OK
This will display with the apostrophe, but you can still add and subtract them if you need to.

HTH,