Apostrophes have their place in Excel; however, when not needed, they can clutter your data and make it difficult to perform calculations or analysis.
Fortunately, Excel offers various methods to remove apostrophes, ensuring your data remains clean and ready for further manipulation!
You can remove apostrophes in Excel using the FIND and REPLACE function. You can also remove them with the SUBSTITUTE function, Text to Columns wizard or by running a VBA script on your Excel sheet.
In this article, we will explore 4 fast methods to remove apostrophes in Excel and provide you with the tools you need to work efficiently with your data.
Let’s get started!
4 Quick Ways to Remove Apostrophes in Excel
In this section, we’ll go over the below methods:
How to remove apostrophe in Excel using the FIND and REPLACE function
How to Remove Apostrophe in Excel Using the Text to Columns Wizard
How to remove apostrophe in Excel using the SUBSTITUTE function
How to remove apostrophe in Excel using VBA code.
1) How to Remove Apostrophe in Excel Using FIND and REPLACE Function
The FIND and REPLACE function is one of the most popular methods to get rid of apostrophes in Excel. To remove an apostrophe in Excel using the FIND and REPLACE function, follow the steps below:
Select the data range from which you want to remove the apostrophe.
Press Ctrl + H on your keyboard to open the Find and Replace dialog box. free excel
In the Find what field, type a single quote (‘), which is the Excel default symbol for an apostrophe.
Leave the Replace with field blank.
Click Replace All.
Click OK on the confirmation message box.
The single quotes (apostrophes) in the selected data range will be removed. Let’s see an example of this in the following section.
Example 1: How to Remove Apostrophe from Excel Data
In this example, we’ll go over how to remove apostrophes from Excel data. Suppose you have a dataset as shown below:
If you want to remove the apostrophes from the dataset, follow the steps below:
Step 1: Select the Data Range
Select the data range from which you want to remove the apostrophes.
Step 2: Open the Find and Replace Dialog Box
Press Ctrl + H to open the Find and Replace dialog box.
Step 3: Replace the Apostrophe with a Blank Space
In the Find What field, type in the apostrophe (‘). Leave the Replace with field blank.
Click Replace All.
You will now have the apostrophes removed from the data set, as shown below.
Note: If the apostrophe you want to remove is a leading apostrophe (first character in the word), this method will not work. This is because leading apostrophes force Excel to treat the contents of the cell as text value instead of a date or numeric value.
So, the contents are formatted as text and the apostrophe is hidden, making it impossible for the Find and replace function to find it. We’ll discuss how you can replace this sort of apostrophe in the next section.
2) How to Remove Apostrophe in Excel Using the Text to Columns Wizard
The Text to Columns wizard is a great way to remove leading apostrophes in Excel. It treats the apostrophe as a delimiter and removes it, while converting the text back into the intended format.
Let’s demonstrate how you can use it in an example below:
Example 2: How to remove apostrophe with the Text to Columns Wizard
Step1: Select the data range.
Highlight the column you want to remove the leading apostrophes from.
In this one, we are working on the Record Harvest Column.
Step 2: Open the Text to Column Feature
Click on the Data tab and select the Text to Columns feature.
Step 3: Select the Delimiter
In the dialog box that comes up, select the Delimiter option.
In the next page that comes up, untick all the other delimiter boxes and set the Text Qualifier to an apostrophe (‘).
Step 4: Remove the apostrophe
After you’ve set the delimiter, click on Finish.
Repeat the same for the Harvest Date Column.
Your data will be cleaned and all the leading apostrophes will be removed while keeping the data in the same column.
3) How to Remove Apostrophe in Excel Using SUBSTITUTE Function
The SUBSTITUTE function is useful when you want to remove apostrophes from a specific cell or range of cells in your Excel sheet. This function replaces instances of a specific character or text within a cell with another character or text.
To remove an apostrophe in Excel using the SUBSTITUTE function, follow the steps below:
Select a blank cell next to the cell containing the apostrophe you want to remove.
Enter the formula below:
=SUBSTITUTE(cell_reference, “‘”, “”).Replace cell_reference with the reference to the cell containing the apostrophe.
Press Enter.
The cell will now display the original text without the apostrophe.
Now, let’s look at an example of using the SUBSTITUTE function to remove an apostrophe in Excel.
Example 3: Using SUBSTITUTE Function to Remove Apostrophe in Excel
In this example, we’ll go over how to use the SUBSTITUTE function to remove apostrophes in Excel. Suppose you want to remove the apostrophe from cell A2 of a dataset.
To do this, follow the steps below:
Step 1: Enter the Formula
Select a blank cell, say B2, next to the cell containing the apostrophe you want to remove. In this case, it’s cell A2.
Enter the formula
=SUBSTITUTE(A2, "'", "")This formula replaces the apostrophe with an empty string.
Step 2: Press Enter
Press Enter to apply the formula. Now, you have successfully removed the apostrophe from cell A2, as shown below:
Drag the fill handle to apply the substitute formula to the rest of the column.
4) How to Remove Apostrophe in Excel Using VBA Code
If you’re dealing with a large dataset, manually removing apostrophes using the SUBSTITUTE function or Find and Replace can be time-consuming. In such cases, you can use VBA (Visual Basic for Applications) code to automate the process.
The VBA code to remove an apostrophe is straightforward. You simply need to loop through the cells in a specified range and apply the SUBSTITUTE function to all the cells. numerical values
Here is an example of VBA code to remove an apostrophe:
Sub RemoveApostrophes()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
cell = Application.WorksheetFunction.Substitute(cell, "'", "")
Next cell
End SubThe above VBA code uses the Application.WorksheetFunction.Substitute function to replace apostrophes with an empty string. Let’s see a practical example of using VBA code to remove apostrophes in this dataset.
Example4: Using VBA Code to Remove Apostrophes in Excel
In this example, we’ll go over how to use VBA code to remove apostrophes in Excel.
Step 1: Open the VBA Editor
Press Alt + F11 to open the VBA Editor.
Step 2: Insert a New Module
In the VBA Editor, go to the Insert menu and select Module.
Step 3: Enter the VBA Code
Paste the following VBA code into the new module:
Sub RemoveApostrophes()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
cell = Application.WorksheetFunction.Substitute(cell, "'", "")
Next cell
End SubStep 4: Run the Macro
To run the macro, press F5 or go to the Run menu and select Run Sub/UserForm.
After running the macro, Excel will check all the cells for apostrophes and remove them.
You’ll notice that the apostrophes in the specified range have been removed, including the new ones. Now, all the dates and numbers have been converted properly into date and numeric values.
That’s it! With the power of VBAs, you have successfully removed the apostrophes from the specified range. To learn more about how you can apply this powerful tool, you can check out this free VBA course from Programming Knowledge on YouTube.
Final Thoughts
Removing apostrophes in Excel is an essential skill for ensuring clean, consistent data. Whether you’re dealing with large datasets or preparing information for analysis, knowing how to eliminate unwanted characters can save you time and frustration.
Go ahead and apply these techniques to your own projects, and you’ll find that working with Excel data is not only easier but also more enjoyable!
If you’d like to learn how to make the most of the new, cutting-edge data analytics tools, check out the video below:
Frequently Asked Questions
How do I remove apostrophes from numbers in Excel?
To remove apostrophes from numbers in Excel, you can use the VALUE function. This function converts a text string that represents a number to a number. Here’s an example:
=VALUE(A1)Replace A1 with the cell reference containing the number with an apostrophe.
How do I remove apostrophes in a range of cells in Excel?
To remove apostrophes in a range of cells in Excel, you can use the SUBSTITUTE function combined with the TEXT function. Here’s an example:
=SUBSTITUTE(TEXT(A1, “0”), “‘”, “”)Replace A1 with the first cell in the range containing the text with apostrophes. Drag the formula down to apply it to the entire range.
How to remove leading apostrophes in Excel?
To remove leading apostrophes in Excel, you can use the TRIM function combined with the SUBSTITUTE function. Here’s an example:
=TRIM(SUBSTITUTE(A1, “‘”, “”))Replace A1 with the cell reference containing the text with a leading apostrophe.
How to remove apostrophes at the beginning of text in Excel?
To remove apostrophes at the beginning of text in Excel, you can use the MID and IF functions. Here’s an example:
=IF(MID(A1, 1, 1) = "'", MID(A1, 2, LEN(A1)), A1)Replace A1 with the cell reference containing the text.
This formula checks if the first character is an apostrophe and removes it if true. Otherwise, it leaves the text unchanged.
How to remove apostrophes in Excel not working?
If the formula to remove apostrophes in Excel is not working, it’s likely due to an incorrect cell reference or syntax error. Double-check that the formula is written correctly and that the cell reference points to the correct cell.
If the formula still isn’t working, you can try using the Text to Columns feature. Go to the Data tab, click on Text to Columns, and choose Delimited. Then, select the apostrophe as the delimiter and click Finish.
