=CHAR(39)&A2<\/code><\/pre>\n\n\n\nYou 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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Leading and trailing apostrophes<\/h2>\n\n\n\n Adding an apostrophe at the beginning is very similar to the previous example, and looks like this.<\/p>\n\n\n\n
=\"'\"&A2&\"'\"\n=CHAR(39)&A2&CHAR(39)<\/code><\/pre>\n\n\n\n <\/figure>\n\n\n\nThe CONCAT function<\/h2>\n\n\n\n You can use the CONCAT\nfunction<\/strong>. It will join multiple strings into a single one.<\/p>\n\n\n\nApostrophe at the beginning:<\/p>\n\n\n\n
=CONCAT(\"'\", A2)<\/code><\/pre>\n\n\n\nApostrophe at the beginning and the end:<\/p>\n\n\n\n
=CONCAT(\"'\", A2, \"'\")<\/code><\/pre>\n\n\n\nVBA<\/h2>\n\n\n\n If you want, you can add apostrophes to the same column that\ncontains values, instead of creating an additional one.<\/p>\n\n\n\n
This formula does it.<\/p>\n\n\n\n
Sub AddAnApostrophe()\n For Each cell In Selection\n cell.Value = \"'\" & cell.Value\n Next cell\nEnd Sub<\/code><\/pre>\n\n\n\nThis is how you can use this formula.<\/p>\n\n\n\n
Select cells to which you want to add apostrophes and run\nthe code.<\/p>\n\n\n\n <\/figure>\n\n\n\nLet’s modify the procedure, so it will add a visible apostrophe to the values.<\/p>\n\n\n\n
Sub AddVisibleApostrophe()\n For Each cell In Selection\n cell.Value = \"''\" & cell.Value\n Next cell\nEnd Sub<\/code><\/pre>\n\n\n\nThis is the result.<\/p>\n\n\n\n <\/figure>\n\n\n\nIn a similar way, you can modify the procedure to add apostrophes also at the end.<\/p>\n\n\n\n
Sub AddAnApostrophe()\n For Each cell In Selection\n cell.Value = \"''\" & cell.Value & \"'\"\n Next cell\nEnd Sub<\/code><\/pre>\n\n\n\nImmediate window<\/h2>\n\n\n\n There is a tool in Excel, called Immediate Window<\/strong>. Many people don’t know about it, but it can be\nuseful as you can write commands and execute them on the spot. Let’s use it in\nthis example.<\/p>\n\n\n\nFirst, you have to open VBE by pressing Left Alt + F11<\/strong> and then Ctrl + G<\/strong> to open the Immediate window.<\/p>\n\n\n\n <\/figure>\n\n\n\nNow, enter the following code and press Enter<\/strong>.<\/p>\n\n\n\nfor each v in range(\"C1:C2\") : v.value = \"'\" & v.value : next<\/code><\/pre>\n\n\n\nThis code works the same way as the first procedure AddAnApostrophe<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"If you want to add an apostrophe in Excel, you can do it using a simple formula. Place it into cell B2. There…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[190],"yoast_head":"\n
Add an Apostrophe in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n