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.

Opening the Text to Column feature to remove single quotes in Excel

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.

Using Text to Column feature to remove single quotes in Excel

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.

Applying the SUBSTITUTE Function to Remove Single Quotes in Excel


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.

Using the SUBSTITUTE Function with Fill Handle icon to Remove Single Quotes in Excel

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.

Paste (Values) the SUBSTITUTE Function to Remove Single Quotes in Excel

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.



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.

Multiply Cells with 1 to Remove Single Quotes in Excel

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.

Use Text Format to Remove Single Quotes in Excel

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.

Using Paste Special feature in Excel

The Paste Special window will appear instantly.

Step_9: Now select Formats as the Paste option.

Step_10: Lastly, click OK to apply it.

Using Formats option from Paste Special window in Excel

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.

SelectING Ignore Error from the drop-down menu in Excel

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.



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 Sub

Usage 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.

Opening Module in the Visual Basic Editor in Excel

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.

Using Macro in Excel

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.

Using Flash Fill Feature to Remove Single Quotes in Excel

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

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *