When we importing data from other source into Excel we may import quotes or quotations from initial file. And we want to remove these quotes from current file, if we remove them one by one it needs a lot of work, so we try to find a simple way to remove all of them by one time. This article will show you two ways of removing quotes, by SUBSITITUTE function or Replace function in tool bar, you can select one you like to implement.

1. Video: Remove quotes for Text or Strings in Excel

This video tutorial will demonstrate a simple and efficient method for removing quotes from text or strings in Excel, making your data cleaner and easier to work with.

2. Remove Quotes by SUBSITITUTE Function

We may already have learned about SUBSITITUTE function in previous article. It can be used for replacing specific strings by others. If the quotation marks are always at the begining and end of the text string, you can use the SUBSTITUTE functio to remove them. Just do the following steps:

Step1: select one blank cell B2 where you want to place the result. then type the following formula:

=SUBSTITUTE(A1,""""," ")

In this case we want remove quotes so we can just replace quotes with space. So enter “” as old text, and leave a space as new text.

Step2: Click Enter, then we can find the quotes are removed.

Step3: you can drag the AutoFill Handle down to cell B2 to B3 to apply this formula. Now we can see that all quotes should be removed.

Step4: If we only want to remove the second quote, we can add a new parameter into the formula. Type the following formula:

=SUBSTITUTE(A1,""""," ",2)

From the above screenshot, you can see that only second quotation marks be removed.

3. Remove Quotes by Replace Function in Tool Bar

This way is much easier to learn for the users that are not familiar with SUBSITITUTE function in Excel. If you have a lot of text to remove quotes, you can use the Find and Replace feature to quickly remove them. Just see the following steps:

Step1: Click on Edit->Find->Replace in tool bar. You can also directly enter Ctrl+H simultaneously on keyboard to load Replace window.

Step2: In Replace window, enter quote “ in Find what field, and enter space (just click space on keyboard) in Replace with field.

Step3: Click Replace All button to replace all quotes. All quotes are removed.

4. Remove Quotes for Text or Strings with VBA Code

You can remove quotes from text or strings in Excel using VBA code by using the Replace function to replace all occurrences of quotes with nothing. Just do the following steps:

Step1: Open your Excel workbook and press Alt + F11 to open the Microsoft VBA editor.

Step2: In the Microsoft VBA editor, go to Insert > Module to create a new module.

Step3: Copy and paste the following code into the new module (Module1), then save the module and go back to the Excel workbook.

Sub RemoveQuotes_excelhow()
    Dim targetRange As Range
    Dim cell As Range
    
    ' Prompt the user to select a range of cells to remove quotes from
    On Error Resume Next
    Set targetRange = Application.InputBox("Select a range of cells to remove quotes from", Type:=8)
    On Error GoTo 0
    
    ' Exit if the user cancels or does not select a range
    If targetRange Is Nothing Then Exit Sub
    
    ' Loop through each cell in the target range and remove quotes
    For Each cell In targetRange
        cell.Value = Replace(cell.Value, Chr(34), "")
    Next cell
End Sub

Step4: go to the Developer tab (if you don’t see the Developer tab, go to File > Options > Customize Ribbon and select the Developer checkbox under Main Tabs) and click on Macros Command, then Select RemoveQuotes Macro name, clicking Run command.

Step5: select a range of cells to remove quotes form (e.g. A1:C4). Click on Ok button.

Step6: The code will loop through each cell in the selected range of cells and remove all quotation marks using the Replace function.

5. Conclusion

There are various methods to remove quotes from text or strings in Excel, including using a formula or VBA code. The formula approach uses the SUBSTITUTE function to replace the quotes with an empty string, while the VBA code method loops through a specified range of cells and replaces the quotes with an empty string using the Replace function.

6. Related Functions

• Excel Substitute function
  The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….