6 Ways to Remove Single Quotes in Excel
People may add single quotation marks or apostrophes (‘) to Excel cells for various reasons. One common reason is to treat the value in the cell as text instead of a number or a date. But after adding the single quotation marks (‘), it can be harder to work with the data. Thankfully, there are ways to remove single quotes from Excel cells to ensure smooth data handling. In this article, I will discuss 6 easy ways to remove single quotes in Excel and make sure your data is ready for analysis.
Introduction to the Dataset
In the following dataset, I have 2 columns (With Single Quotation Mark and Without Single Quotation Mark). It is important to note that, the single quotation mark (‘) is present in all cells of the column With Single Quotation Mark, but they are apparently invisible. But, when I select any of these cells and check at the Formula Bar, it is visible there.
Now, I will show how you can remove single quotes (‘) from the cells in Excel.
Easiest Way to Remove Single Quotes in Excel
The fastest way to remove the single quotes (‘) is by using the Text to Columns feature. You can do it within 5 effortless steps.
Usage Guide
Step_1: At first, select your target cell range, cell A2:A10.
Step_2: Secondly, select the Data tab.
Step_3: Now, from the Data Tools group, click on the Text to Columns option.
The Convert Text to Columns Wizard will show up on the Excel sheet.
Step_4: Now select Delimited as the file type.
Step_5: Lastly, hit the Finish button.
Final Result >
Now, the leading zero of the number trail and the single quotes have gone.
Alternative Way #1: Use the SUBSTITUTE Function to Remove Single Quotes in Excel
Another fastest way to remove the single quotes (‘) is by using the SUBSTITUTE function. What I am going to do is, as an alternative to the apostrophes, use a blank space.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Formula
=SUBSTITUTE(A2,” ’ ”, “ “)
Usage Guide
Step_1: At first, place an empty column beside the original column.
I have created a spare column named Without Single Quotation Mark here.
Step_2: Then copy the formula from above.
Step_3: Now, paste it into cell B2.
Formula Explanation
- A2 is the text. It is the cell reference that you want to change or modify.
- “ ‘ ” is the old_text. It is the cell reference that you want to change or modify.
- “ “ is the new_text. It is the mention of the new characters that you want to replace the old_text with.
- [instance_num] is an optional argument. It defines the occurrence of the old_text that you want to replace. If you omit this argument, all instances of old_text will be replaced.
Step_4: Now, copy down the formula using the Fill Handle icon in the cell range B2:B10.
Step_5: Next, select the cell range B2:B10.
Step_6: Press CTRL+C to copy the cell range B2:B10.
Step_7: Then, right-click on the top cell B2.
This will open the context menu.
Step_8: Lastly, from the context menu, select the Paste (Values) option.
Final Result >
If you want to display the leading zeroes of your inputs, you can use this method. This way, the leading zeroes do not vanish from the results.
- 3 Ways to Remove Specific Characters in Excel
- 5 Ways to Remove Blank Characters in Excel
- 5 Ways to Remove Numeric Characters from Cells in Excel
Alternative Way #2: Multiply Cells with 1 to Remove Single Quotes in Excel
Another amazing technique to remove the single quotes (‘) is multiplying your target cell range with 1 and after that, returning them as values in the cell addresses.
Usage Guide
Step_1: First, type 1 in a separate cell beside the dataset.
I input 1 in cell A12.
Step_2: Then, press CTRL+C to copy cell A12.
Step_3: Now, select your target cell range A2:A10.
Step_4: Next, right-click on the top cell A2.
The context menu will pop up.
Step_5: Click on the Paste Special option.
The Paste Special window will show up on your Excel sheet.
Step_6: Now, select Values as the Paste option and select Multiply as the Operation option.
Step_7: Lastly, Click OK.
Final Result >
You will see the apostrophes are no longer present in the dataset.
Alternative Way #3: Use Text Format to Remove Single Quotes in Excel
You can also format the cells in the text format in Excel to remove single quotes. Now, follow these steps to do that.
Usage Guide
Step_1: Take a spare cell beside your dataset.
I randomly choose cell C2.
Step_2: Next, go to the Home tab.
Step_3: Now, from the Number group, click on the Number Format drop-down menu.
Step_4: From the list, select Text.
Step_5: Now copy cell C2 by pressing the CTRL+C key.
Step_6: After that, select the cell range A2:A10.
Step_7: Then, on the top cell A2, right-click to open the context menu.
Step_8: Go to Paste Special from the menu.
The Paste Special window will appear instantly.
Step_9: Now select Formats as the Paste option.
Step_10: Lastly, click OK to apply it.
The cells are now in text format and do not have single quotes anymore.
However, if you are worried about the green little triangle signs, these are alert signs.
Green Error Signs
Green error signs appear to alert you to certain actions. But they don’t interfere with any functionality on the spreadsheet. When numbers are saved as text in a cell, Excel often detects this and displays an alert next to the cell. When you see a green error sign in a cell, you can click on the cell to see a drop-down menu that provides information about the error and suggests potential solutions.
You can remove the error signs by following these two steps.
Step_11: Click on the yellow alert button located in the top-left corner of the top cell.
Step_12: Then select Ignore Error from the drop-down menu.
Final Result >
By turning the cells into text format, the numbers have shifted on the left side of the cells. If you wish you can move them to the right side again.
- 7+ Methods to Remove Characters from Left in Excel
- 5 Approaches to Remove Characters from the Right in Excel
- 3+ Ways to Remove Dashes in Excel
Alternative Way #4: Use VBA Codes to Remove Single Quotes in Excel
If you want to use VBA codes to remove the single quotes (‘) from your dataset in Excel, you can use this specific code. Just Copy the code from here and insert the code inside a new Module in your Visual Basic Editor. Finally, Run it. Done!
Sub RemoveSingleQuotes()
Dim cell As Range
For Each cell In Selection.Cells
cell.Value = Replace(cell.Value, "'", "")
Next cell
End SubUsage Guide
Step_1: Press ALT + F11 to open the Visual Basic Editor.
Step_2: Now right-click on the sheet name.
Step_3: Now, choose Insert > Module.
This will create a new Module.
Step_4: Now, copy the code from above
Step_5: Now paste it into the Module.
Step_6: Go back to your sheet.
Step_7: Select the cells containing the single quotes you want to remove.
Step_8: Then, click the Macros button on the Developer tab.
The Macro window will pop up immediately.
Step_9: Now, select the RemoveSingleQuotes macro.
Step_10: Finally, click Run.
Final Result >
The VBA has perfectly done the job of removing single quotes from the cells in Excel.
Alternative Way #5: Use Flash Fill Feature to Remove Single Quotes in Excel
The technique I am going to show you is using the Flash Fill feature to remove the apostrophes (‘) from the Excel sheets. Flash Fill helps to automatically fill values based on patterns it recognizes in the data. For that reason, you have to manually input the first cell.
Usage Guide
Step_1: On cell B2, type the raw input of cell A2 without the single quotation mark (‘).
Here in the column With Single Quotation Mark, you may not see the single quotes before each cell inputs, but they are present. In cell B2, I wrote the same input as cell A2, just without a single quote.
Step_2: Now, go to the Data tab.
Step_3: Lastly, from the Data Tools group, hit the Flash Fill icon.
Final Result >
The Flash Fill correctly recognized the output pattern and returned all the cell values without the single quotes in the column Without Single Quotation Mark.
Conclusion
In conclusion, there are several ways to remove single quotes in Excel. Whether you prefer to use built-in functions or VBA code or Flash Fill feature, Excel provides a variety of options for manipulating your data. By following the methods outlined in this article, you can easily remove single quotes from your cells in Excel. I hope you find this article helpful. To read more about removing specific characters in Excel, click here. And also, feel free to comment in the comment section if you find anything confusing. Thanks for reading!
Related Articles
![Remove Non-Printable Characters in Excel [5+ Methods]](https://excelgraduate.com/wp-content/uploads/2022/05/Remove-Non-Printable-Characters-in-Excel-23.png)
