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,