puting an apostrophe before a number

Mr Mayor

New member
Joined
May 13, 2011
Messages
1
Reaction score
0
Points
0
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
 
Code:
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
 
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,
 
Back
Top