If you have worked on Excel files extracted from a database or a system, you probably have encountered cells with leading apostrophes. If you’re like me, you too might have wondered why in the world they’re there.
It turns out that the apostrophe (or single quote) (‘) that you see at the beginning of a cell is actually a special character. It tells Excel to treat the remaining contents of the cell as text.
A cell is automatically treated as a text (regardless of its contents — may it be a date or a number) if it begins with an apostrophe.
What’s interesting about this character is that you don’t immediately see it when you look at the cells. You only see it from the formula bar (as shown below).
Adding this special character is useful if you intend to:
- Retain the leading zeroes in numbers (e.g., 000421).
- Add the month and year without having Excel automatically convert it as a date.
On the other hand, if you intend to use these cells on formulas or if you want to change the formatting of these cells, you will need to first remove the leading apostrophes. This step is crucial. Missing this will cause your formulas or cell formatting to not work as expected.
Below are varying methods that you could choose from to remove these leading apostrophes. Each of them has its pros and cons – so choose whatever best fits your needs.
Table of Contents
1. Using“Text to Columns” option
This method is perfect if there’s only one column you need to convert or remove the leading apostrophes from. If you need to convert more than one column, please proceed to the next options.
1. Highlight the entire column you need to convert (or remove the leading apostrophes from).
2. Click on the Data menu. Go to the Data Tools section and click on Text to Columns.
3. The Convert Text to Columns Wizard will appear.
From the options, select Delimited and click Finish.
4. That’s it! All the leading apostrophes inside the cells should be removed, and your column should now be in its correct format.
In my case, the column has been converted into a date format.
2. By multiplying the cells by 1
If you’re looking at removing the leading apostrophes from cells containing numbers, this method is for you.
IMPORTANT: This method does not work on cells containing dates and texts.
1. Type ‘1’ to any cell in your worksheet.
2. Select that cell and press CTRL + C to copy.
3. Select all the cells with leading apostrophes.
4. Press CTRL + ALT + V to open the Paste Special menu.
From the Operation options, select Multiply and click OK.
5. That’s it! You should see the selected cells converted into numbers.
Related Tutorial: How to Remove Commas in Excel
3. By copying and pasting the cells as values
This method is probably my favorite as it works on any cell content – a number, a date, or a text. It also works on multiple columns.
The only requirement for this method is that you must copy the cells to new columns. I would recommend pasting them all together onto a new sheet so that the formatting of the cells is in the default format, which is General.
1. Highlight all the cells with leading apostrophes. You could include the headers if you want to.
2. Press CTRL + C to copy.
3. Go to a new sheet.
4. Select the topmost cell of the column where you’d like to add the cells with no apostrophes.
5. Press CTRL + ALT + V to open the Paste Special menu.
From the Paste options, select Values and click OK.
6. That’s it! All the leading apostrophes should now be removed from the cells.
You can now add the appropriate cell formatting for each column.
You can now also use these cells in your formulas.
Related Tutorial: How to Remove Leading Zeroes in Excel
4. Using the VALUE() and DATEVALUE() functions
If you prefer to preserve the cells with leading apostrophes, and instead, add formulas to convert them, you can make use of the VALUE() and DATEVALUE() functions in your formula.
IMPORTANT: This method only works on numbers and dates. It does not work on texts.
These two functions are practically the same. The only difference is that the DATEVALUE() function only works on cells that contain texts that look like dates.
The VALUE() function can work on both numbers and dates.
These two functions basically convert a text string into its numerical version.
In my example below, notice that cells A to D are all in text format because they have a leading apostrophe in the cells.
My goal is to get the total amount by multiplying the No. of Units with the Price without changing the format of these two columns.
To do this, I’ll use this formula: =VALUE(C2) * VALUE(D2)
As you can see, the correct value still appears even if the cells referred to are in text format.
On the other hand, if you want to convert a date in text format into an actual date, you can use either the VALUE() or DATEVALUE() function.
In my example below, I used the DATEVALUE() function. Notice that the result are all numbers.
These numbers represent a date in a Microsoft Excel date format, between 1/1/1900 or 1/1/1904 (depending on the workbook’s date system) and 12/31/9999.
The next step is to convert this column into date format so that they will all look like actual dates.
Select the entire column and press CTRL + 1 to open the Format Cells menu.
From the list of Categories, select Date.
Then select the type or date format that you prefer and click OK.
That’s it! The dates in text format are now converted into actual dates.
5. Automatically remove leading apostrophes using VBA
If you regularly receive files with leading apostrophes and would prefer having a macro to do the conversion or the apostrophe removal for you, then this final method is for you.
1. Activate the worksheet that contains cells with leading apostrophes.
2. Press ALT + F11. This should open the VBA Editor.
3. Click on the Insert menu and select Module.
4. A new module will be inserted.
5. Copy the following code and paste it into this module.
Sub RemoveApostrophesInActiveSheet()
Dim cl As Range
With ThisWorkbook.ActiveSheet
For Each cl In .UsedRange
If cl.Value <> "" Then
cl.Value = Replace(cl.Value, "'", "")
End If
Next
End With
End Sub
6. Once pasted, press F5 to run the code.
7. Close the VBA editor. You should see all the leading apostrophes removed from the active sheet.
If you want to do it on another sheet, activate that sheet first.
Open the VBA Editor again by pressing ALT + F11.
Press F5 to run the code. That’s it!
Related Tutorial: How to Remove Parentheses in Excel
Conclusion
The apostrophe at the beginning of a cell serves the special purpose of converting the cell content into text. If, however, you prefer converting the cell into its actual format, you can easily remove the apostrophe using any of the options provided above.