Remove text between quotes, variable length and number of quotes

**JP Romano** · 10-14-2011, 09:30 AM

Hi all... I'm attempting to clean up some rather ugly strings, and would like to automate a process to clear out all the data with the exception of what's between quotations.

The strings are variable length, and could have anywhere from 0 to 6 sets of quotations.

For example

From this

aliasid:6558 enabled:0 yk:2 aclass:1 ticker:"SHE US" aliastext:"SHENGKAI" strength:1003 chgid:1826630 usage:2

I want to be left with

SHE US SHENGKAI

Because this will sit in the middle of a larger macro, I'd much prefer to do this with vba code than a formula, but if the formula is the way to go, I can work with it.

Thank you so much for any help!

**Andrew-R** · 10-14-2011, 09:42 AM

How's this for you?

Sub GetQuoted()

Const sDELIMITER = " "

Dim sTestString As String
Dim avSplitText As Variant
Dim lLoop As Long
Dim sTmpString

sTestString = "aliasid:6558 enabled:0 yk:2 aclass:1 ticker:" & Chr(34) & "SHE US" & Chr(34) & "aliastext:" & Chr(34) & "SHENGKAI" & Chr(34) & " strength:1003 chgid:1826630 usage:2"
sTmpString = ""

avSplitText = Split(sTestString, Chr(34))

For lLoop = LBound(avSplitText) + 1 To UBound(avSplitText) Step 2
  sTmpString = sTmpString & avSplitText(lLoop) & sDELIMITER
Next lLoop

If Len(sTmpString) > 0 Then
  sTmpString = Left(sTmpString, Len(sTmpString) - Len(sDELIMITER))
End If

MsgBox sTmpString

End Sub

**JP Romano** · 10-14-2011, 09:50 AM

Made minor modifications and it worked like a charm. THANK YOU!

**snb** · 10-14-2011, 10:05 AM

Sub snb()
 c00 = "aliasid:6558 enabled:0 yk:2 aclass:1 ticker:""SHE US"" aliastext:""SHENGKAI"" strength:1003 chgid:1826630 usage:2"
 msgbox  Join(Filter(Split(Replace("~" & c00, Chr(34) & Space(1), Chr(34) & "~"), Chr(34)), "~", False))
End Sub

**Andrew-R** · 10-14-2011, 10:08 AM

Ah, snb, we don't seem to have been in a thread together for a while. I've missed you, but then I've a real soft spot for any line of code that begins Join(Filter(Split(Replace