If you want to add an apostrophe in Excel, you can do it using a simple formula. Place it into cell B2.
|
1 |
="'"&A2 |
There is another, simple method. You can use the CHAR function instead of apostrophes. We need to pass a code that represents apostrophes. This code is 39.
Now, the formula is going to look like this.
|
1 |
=CHAR(39)&A2 |
You may want to consider using this one because it seems cleaner than the previous one. This method has a disadvantage in that you must remember the code.
Both formulas are going to convert a value to a text. In our example, the cell with text stays text, and the cell with a number is converted to text.
Leading and trailing apostrophes
Adding an apostrophe at the beginning is very similar to the previous example, and looks like this.
|
1 2 |
="'"&A2&"'" =CHAR(39)&A2&CHAR(39) |
The CONCAT function
You can use the CONCAT function. It will join multiple strings into a single one.
Apostrophe at the beginning:
|
1 |
=CONCAT("'", A2) |
Apostrophe at the beginning and the end:
|
1 |
=CONCAT("'", A2, "'") |
VBA
If you want, you can add apostrophes to the same column that contains values, instead of creating an additional one.
This formula does it.
|
1 2 3 4 5 |
Sub AddAnApostrophe() For Each cell In Selection cell.Value = "'" & cell.Value Next cell End Sub |
This is how you can use this formula.
Select cells to which you want to add apostrophes and run the code.
Let’s modify the procedure, so it will add a visible apostrophe to the values.
|
1 2 3 4 5 |
Sub AddVisibleApostrophe() For Each cell In Selection cell.Value = "''" & cell.Value Next cell End Sub |
This is the result.
In a similar way, you can modify the procedure to add apostrophes also at the end.
|
1 2 3 4 5 |
Sub AddAnApostrophe() For Each cell In Selection cell.Value = "''" & cell.Value & "'" Next cell End Sub |
Immediate window
There is a tool in Excel, called Immediate Window. Many people don’t know about it, but it can be useful as you can write commands and execute them on the spot. Let’s use it in this example.
First, you have to open VBE by pressing Left Alt + F11 and then Ctrl + G to open the Immediate window.
Now, enter the following code and press Enter.
|
1 |
for each v in range("C1:C2") : v.value = "'" & v.value : next |
This code works the same way as the first procedure AddAnApostrophe.