How to Put Apostrophes Around Every Cell in a Column
Adding text like an apostrophe around a single cell is a simple process but if you want to add the same text to a large number of cells, the process becomes tedious and time consuming. Fortunately, there is a quick way to automate it using your spreadsheet's function capability.
Other People Are Reading
Instructions
-
Adding Text Using Functions
-
1
Add two additional columns to the right of the column to which you want to add the apostrophes. Assuming your original column of data is in column A, click on the "A" at the top of the column (to select the entire column "A") and then click the insert button twice to create an empty "B" and "C" column.
-
2
Select the blank cell to the right of the first cell you want to modify. If you want to modify "A1," select cell "B1" by clicking in it.
-
-
3
Click the insert function button or press the equal key (=) to begin a new function. From the list of functions, choose "Concatenate." A "Function Arguments" dialog box will appear with fields for Text 1 and Text 2.
-
4
Enter an apostrophe in the "Text1" box. Click in the "Text2" field. Enter "A1" (without quotation marks) into the Text2 field or click once on cell "A1" in the spreadsheet.
-
5
Insert another apostrophe into the "Text3" field and click the "OK" button to complete your formula. Cell "B1" should now contain a formula that looks like the data in cell "A1" surrounded by apostrophes.
Finishing Columns B and C
-
6
Duplicate cell "B1" by selecting the cell and dragging down the little box in the lower right corner of the cell. Alternatively, you can copy the cell (select it and hit "CTRL-C"), select the entire column ("CTRL-Spacebar") and paste the formula into all the cells ("CTRL-V").
-
7
Copy column "B" by clicking the "B" at the top of the column and then either clicking the copy icon or pressing "CTRL-C."
-
8
Select column "C" by clicking the "C" at the top of the column. Choose "Paste Special" and choose "Values and Formatting." This will take the values from column "B" that include the surrounding apostrophes but eliminate the formulas.
-
9
Delete columns "A" and "B" because you no longer need them. Select both columns by holding the control key and clicking the "A" and then the "B" at the top of each column. Then click the "Delete Cells" or "Delete Column" icon. Column "C" will move over to where column "A" was, and you will have the original column "A" surrounded by apostrophes.
-
1
Tips & Warnings
Some spreadsheet programs are less automated than others, and you may have to manually enter the formula as =CONCATENATE("'",A1,"'") where "A1" is the original cell that you want to add the apostrophes to. Note that the apostrophes are surrounded by quotation marks in the formula.
