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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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,
 
Top