CODEWITHSUNDEEP
excelvbaexcel-formula
shantam malgaonkar
shantam malgaonkar

Reputation: 13

How can i extract the text which is not between Double quotes(" ")? in excel

I have a column which has some strings like these :-

  1. RM125 | RM250 | RMX250 | "RM 125" | "RM 250" |"RMX 250"

  2. DR250 | DR350 | "DR 250" | "DR 350" | DR250S | DR250SE | DR350S | "DR250 S" | "DR250 SE" | "DR350 S"

  3. RMZ250 | "RMZ 250" | "RM-Z 250" | "RMZ-250"

now I want to extract only text which is not between double quotes(" ")

for example:

  1. RM125 | RM250 | RMX250
  2. DR250 | DR350 | DR250S | DR250SE | DR350S

Upvotes: 0

Views: 244

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60289

EDIT Thanks to @PEH for pointing out that my regex would not exclude quoted substrings if they were in the first location (and therefore not preceded by the |). We have to make a special case for an initial quoted substring by changing:

.Pattern = "^""[^""]+""\s*\||\|\s*""[^""]+""\s*"

and I have done so in the code below.

You can use Regular Expressions implemented in VBA:

Function delQuotedStrings(S As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "^""[^""]+""\s*\||\|\s*""[^""]+""\s*" ' changed from "\|\s*""[^""]+""\s*"
        .Global = True
        delQuotedStrings = .Replace(S, "")
    End With
End Function

eg. Data in A1:

B1: =delQuotedStrings(A1)

OR one could use a non-Regex solution:

Option Explicit
Function delQuotedStrings(S As String) As String
    Dim v, w, col As Collection, i As Long
Set col = New Collection
For Each v In Split(S, "|")
    If Not Left(Trim(v), 1) = """" Then _
        col.Add v
Next v

ReDim w(1 To col.Count)
For i = 1 To col.Count
    w(i) = col(i)
Next i

delQuotedStrings = Join(w, "|")

End Function

enter image description here

Explanation of the Regex:

delete quoted substrings

\|\s*"[^"]+"\s*

Created with RegexBuddy

Upvotes: 1

Related Questions