How to Remove Quotes from a String in Excel VBA?

Sharing is caring!

Do you have text strings in Microsoft Excel that contain unwanted quotation marks? If so, you can easily remove the quotes using Visual Basic for Applications (VBA) in Excel. In this comprehensive guide, we’ll walk you through the process of using VBA code to remove single quotes, double quotes, or both from strings in your Excel spreadsheets.

Whether you’re working with data imported from an external source or combining data from multiple cells, removing excess quote characters is a common data cleaning task. By the end of this article, you’ll have a solid understanding of how to automate this process using Excel VBA.

What is a String in Excel?

Before we dive into the VBA code, let’s quickly review what we mean by a “string” in Excel. In spreadsheet terms, a string refers to any sequence of text characters. Strings are often enclosed in quotation marks, either single quotes (') or double quotes (").

For example:

• 'This is a string with single quotes'
• "This is a string with double quotes"

Sometimes you may have strings where the quotes are part of the actual text content, but other times the quotes may be extraneous and need to be stripped out. Thankfully, Excel VBA provides built-in functions we can use to easily remove unwanted quotes from string values.

Using VBA to Remove Quotes from a String

Microsoft Visual Basic for Applications, or VBA, is a programming language used to automate tasks and extend the functionality of Microsoft Office applications like Excel. We can write VBA macros (also called subroutines) in the Visual Basic Editor (VBE) to manipulate data in Excel.

To remove quotes from a string in Excel using VBA, we will use the built-in Replace function. Replace allows us to find and replace specified characters within a text string.

Setting Up the VBA Code

To get started, open up the Visual Basic Editor in Excel by pressing Alt+F11 on Windows or Option+F11 on a Mac. This will open the VBE in a new window.

In the VBE, insert a new module by going to Insert > Module on the menu bar. This is where we’ll write our reusable VBA subroutine to remove quotes from strings.

Start by creating a new subroutine called “RemoveQuotesFromString”:

Sub RemoveQuotesFromString()
    'Your code will go here
End Sub

Any code we write between the Sub and End Sub lines will execute when we run this macro.

Removing Double Quotes

Let’s start with a basic example of using Replace to remove double quotes (") from a string.

Between the Sub and End Sub lines, add the following code:

MyString = """These double quotes will be removed."""
MyString = Replace(MyString, """", "")

'Result: MyString = "These double quotes will be removed."

Here’s what this code does:

1. The first line creates a string variable called MyString and assigns it the value """These double quotes will be removed.""". Notice the quotes are doubled up – this is how we escape quote characters within a string literal in VBA.
2. The second line calls the Replace function to modify MyString:
   • The first argument is the input string (MyString)
   • The second argument is the character sequence to find (""")
   • The third argument is the replacement character sequence ("")
3. Replace searches for all occurrences of """ (a pair of double quotes) within MyString and replaces them with "" (an empty string). This effectively removes the double quotes from the string.
4. Finally, the result is stored back in MyString. The original string "These double quotes will be removed." now has the surrounding quotes removed.

Removing Single Quotes

The process for removing single quotes is nearly identical – we just need to use single quote characters (') instead of double quotes.

Add the following code to your subroutine:

MyString = "'These single quotes will be removed.'"
MyString = Replace(MyString, "'", "")

'Result: MyString = "These single quotes will be removed."

This code assigns MyString the value 'These single quotes will be removed.', then uses Replace to find all single quotes (') and replace them with an empty string (""), thus removing the quotes.

Removing Both Single and Double Quotes

What if your string contains a mix of both single and double quotes that all need to be removed? No problem! We can simply chain two Replace calls together.

Add this code to your VBA subroutine:

MyString = "'This string has both "double quotes" and 'single quotes'.'"
MyString = Replace(MyString, """", "") 
MyString = Replace(MyString, "'", "")

'Result: MyString = "This string has both double quotes and single quotes."

The string 'This string has both "double quotes" and 'single quotes'.' contains both types of quotes. The first Replace removes any double quotes by replacing """ with "". The second Replace then removes any single quotes by replacing ' with "".

The order of the Replace calls doesn’t matter, since each call operates on the updated MyString value. After both calls, we’re left with just the string content itself without any extraneous quote characters.

Removing Quotes from a Range of Cells

In the examples so far, we’ve been working with quotes in a single string variable. But what if your string data is spread across multiple cells in an Excel worksheet?

To remove quotes from a range of cells, we can use VBA to loop through the cells and apply the Replace function to the value in each cell.

Assume you have string data in cells A1 through A10. Add this code to your VBA subroutine to remove any single or double quotes from those cells:

Sub RemoveQuotesFromCells()
    Dim cell As Range

    For Each cell in Range("A1:A10")
        cell.Value = Replace(cell.Value, """", "")
        cell.Value = Replace(cell.Value, "'", "") 
    Next cell
End Sub

Here’s a breakdown of what this code does:

1. The Dim statement declares a variable called cell of type Range. This will represent each individual cell as we iterate through them.
2. The For Each loop cycles through every single cell in the range A1:A10.
3. For each cell:
   • The first line replaces any double quotes in the cell’s Value with an empty string, effectively removing the double quotes.
   • The second line replaces any single quotes in the cell’s Value with an empty string, removing the single quotes.
4. The Next cell line signals the end of the loop. Excel will go back to step 3 and process the next cell in the range until all cells have been looped through.

After running this macro, any cells in the range A1:A10 containing single or double quotes will have those quotes stripped out. The original cell values will be overwritten with the quote-less versions.

Troubleshooting Quote Removal Issues

If your quotes aren’t being removed as expected, here are a few troubleshooting tips:

• Double-check that you’re using the correct type of quotation marks in your Replace function. If you copied the string from another source, it may contain curly “smart quotes” (' ') instead of straight quotes (''). Replace will only find and remove the quote characters you specify.
• If your string has multiple nested quotes, like ""'This "example' string"", you may need additional Replace calls to catch them all. Work from the inside out, removing the innermost quotes first.
• Watch out for leading or trailing spaces around the quotes. If your strings look like " 'example' ", the extra spaces will prevent the quotes from being removed. To fix this, use the Trim function to get rid of spaces before doing the quote replacement:

MyString = "  'This string has quotes and spaces'  "
MyString = Trim(MyString) 'remove extra spaces first
MyString = Replace(MyString, "'", "") 'then remove quotes

'Result: MyString = "This string has quotes and spaces"

Final Thoughts

As you can see, removing unwanted quote characters from strings in Excel is a straightforward process using VBA. With the Replace function, we can easily find and remove single quotes, double quotes, or both with just a few lines of code.

Whether you need to clean up quotes in a single string variable or remove them from an entire column of cells, the basic steps are:

1. Identify the specific quote characters to remove
2. Use Replace to find those quotes and swap them out for an empty string
3. Repeat with additional Replace calls as needed to catch any remaining quotes

FAQs

For Each cell In Range("A1:A10")
    cell.Value = Replace(cell.Value, """", "")
    cell.Value = Replace(cell.Value, "'", "")
Next cell

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.