CODEWITHSUNDEEP
vbaexcel
Leigh
Leigh

Reputation: 11

Excel VBA: I want to take the text in between quotation marks out of a cell.

so say I have a cell with the following text:

The man "BOB" leaves his "home".

How do I make it so that the cell just says

The man leaves his home

I know that there are a number of ways to do this, but none of them seem easy. I could create a do loop that examines each cell character by character and removes the quotation marks that way.

I could also create a text to columns macro, so it'd look like this after:

The man Bob leaves his Home

But my issue would be how would I figure out which text that I would need to delete. I realize that my example makes no sense when the words are extracted. I'm guessing that there is a formula or something that I am forgetting here?

Upvotes: 0

Views: 1390

Answers (1)

RiPs7
RiPs7

Reputation: 29

I'm not sure if this could be done "easily" with excel's built-in functions. However, you could achieve that by using VBA code. In a module you could write a function like this:

Public Function ReplaceInQuotes(ByVal str)
    Set regex = CreateObject("vbscript.regexp")
    regex.Global = True
    regex.IgnoreCase = True
    regex.Pattern = """.+?"""
    Dim result
    result = regex.Replace(str, "")
    regex.Pattern = "\s+"
    result = regex.Replace(result, " ")
    ReplaceInQuotes = result
End Function

To call the above function you can write a small sub like this:

Public Sub main()
    Cells(1, "B").Value = ReplaceInQuotes(Cells(1, "A").Value)
End Sub

So, let's say if you have your string in cell A1, then in B1 after executing the main sub you will have your result. The function ReplaceInQuotes works with regular expressions so you might need to manipulate it a bit to deal with some "double" spaces that might appear.

Upvotes: 1

Related Questions