Insert apostrophe in front of numbers
This tutorial shows how to add an apostrophe (') in front of a numeric cell, meaning the cell comprises only numbers without any other characters of spaces, through the use of an Excel formula, with the IF and ISNUMBER functions or VBA
Hard coded formula
Cell reference formula
|
GENERIC FORMULA
=IF(ISNUMBER(value)=TRUE,"'"&value,value)
ARGUMENTS GENERIC FORMULA
=IF(ISNUMBER(value)=TRUE,apostrophe&value,value)
ARGUMENTS EXPLANATION This formula uses the IF and ISNUMBER functions with the & sign to insert an apostrophe in front of a numeric cell.
Click on either the Hard Coded or Cell Reference button to view the formula that either has the apostrophe directly entered in the formula or referenced to a cell that captures the apostrophe sign that is to be inserted between the values that you want to combine.
By using the excel method the number is converted to text, however, if you want to retain the cell as a numeric value and still insert an apostrophe in front of a number you can use the following VBA approach. |
Hard coded against single cell
Sub Insert_apostrophe_in_front_of_numbers()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert an apostrophe in front of a number
If IsNumeric(ws.Range("B5")) = True Then
If IsNumeric(ws.Range("B5")) = True Then
ws.Range("C5") = "'" & ws.Range("B5")
Else
ws.Range("C5") = ws.Range("B5")
End If
End Sub
Cell reference against single cell
Sub Insert_apostrophe_in_front_of_numbers()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert an apostrophe in front of a number
If IsNumeric(ws.Range("B7")) = True Then
If IsNumeric(ws.Range("B7")) = True Then
ws.Range("C7") = ws.Range("C4") & ws.Range("B7")
Else
ws.Range("C7") = ws.Range("B7")
End If
End Sub
Hard coded against range of cells
Sub Insert_apostrophe_in_front_of_numbers()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert an apostrophe in front of a number
For x = 5 To 8
If IsNumeric(ws.Range("B" & x)) = True Then
ws.Range("C" & x) = "'" & ws.Range("B" & x)
Else
ws.Range("C" & x) = ws.Range("B" & x)
End If
Next x
End Sub
Cell reference against range of cells
Sub Insert_apostrophe_in_front_of_numbers()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert an apostrophe in front of a number
For x = 5 To 8
If IsNumeric(ws.Range("B" & x)) = True Then
ws.Range("C" & x) = ws.Range("C4") & ws.Range("B" & x)
Else
ws.Range("C" & x) = ws.Range("B" & x)
End If
Next x
End Sub
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Insert comma to end of a cell | How to add a comma (,) to the end of a cell through the use of an Excel formula or VBA | |
| Insert a comma before first number | How to insert a comma (,) before the first number in a cell through the use of an Excel formula or VBA | |
| Insert a comma after first number | How to insert a comma (,) after the first number in a cell through the use of an Excel formula or VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE | |
| ISNUMBER Function | The Excel ISNUMBER function tests a specified value (cell) if it's a numeric value and returns TRUE if it's a text value or FALSE if it's not a numeric value |