Extract States from a String

NoSparks · #2 04-23-2018, 04:22 PM

Assuming the sample string is in A1

Code:

Sub GetStates()
    Dim str As String, i As Integer
    Dim ar As Variant, arr As Variant
    
    str = Range("A1").Text
    str = Replace(Replace(Replace(Replace(Replace(str, "}", ""), "[", ""), "]", ""), """", ""), ",", "")
    ar = Split(str, "{")
    For i = LBound(ar) To UBound(ar)
        If InStr(ar(i), "state") > 0 Then
            arr = Split(ar(i), ":")
            MsgBox arr(UBound(arr))
        End If
    Next i
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Extract a string from a paragraph	kirkm	Word VBA	7	09-11-2016 06:13 PM
How to extract only numbers from a STRING?	Learner7	Excel	3	07-02-2013 06:25 AM
Extract Numbers from Alphanumeric String	OTPM	Excel	6	05-13-2011 12:52 AM
Extract numbers from a text string	aleale97	Excel	4	02-10-2011 10:33 AM
Extract from String using Wildcard	whousedmy	Word	0	05-21-2009 01:35 AM