Extract States from a String

soldat452002 · #1 04-23-2018, 02:14 PM

Hello

I have a database that generates the following string based on a web form selection.

I need to extract the state names only from these strings.

How can I achieve that in Excel? I tried everything

[{"licenseexpdate":"","licensenumber":"ysdksnjdkl", "state":"Pennsylvania"},{"licenseexpdate":"08/12/2020","licensenumber":"lic1235","state":"Florida"} ,{"licenseexpdate":"","licensenumber":"gsdgfsef"," state":"South carolina"}]

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

soldat452002 · #3 04-23-2018, 04:26 PM

That is perfect. How can I append the values to a excel spreadsheet

NoSparks · #4 04-23-2018, 04:53 PM

Sorry, I must have missed what was to be done with the results so just displayed them as they were being extracted in a message box in order to see the results.

You need to replace the MsgBox line with the cell address(s) you're wanting the result(s) to go in to.
For a more accurate/specific answer more information is required.
What's the sheet name? What column? All in one cell or ???

soldat452002 · #5 04-24-2018, 06:04 AM

Quote:

Originally Posted by NoSparks

Sorry, I must have missed what was to be done with the results so just displayed them as they were being extracted in a message box in order to see the results.

You need to replace the MsgBox line with the cell address(s) you're wanting the result(s) to go in to.
For a more accurate/specific answer more information is required.
What's the sheet name? What column? All in one cell or ???

I want to insert the results in a separate column called states next to the string.sheet name is extract.

NoSparks · #6 04-24-2018, 08:16 AM

Sub in attached is commented line by line, hopefully makes sense.
Comment out or remove the lines of the result you don't want.

Hope that helps.

soldat452002 · #7 04-24-2018, 09:11 AM

Okay thanks.

Im assuming I need to change this to my spreadsheet location.

MsgBox arr(UBound(arr))

NoSparks · #8 04-24-2018, 09:39 AM

I'm not sure what (or why) you're asking.

That line isn't in the altered and commented procedure.
It was only in the first procedure to show what the result would be as they were not being written to anywhere.

soldat452002 · #9 04-25-2018, 09:50 AM

I was trying to have the code put into a colum cell on the spreadsheet instead of a msgbox. Im just not familiar with VBA .

ArviLaanemets · #10 04-26-2018, 04:16 AM

In case you get the strings on separate rows for every entry, like
{"licenseexpdate":"","licensenumber":"ysdksnjdk l", "state":"Pennsylvania"}
{"licenseexpdate":"08/12/2020","licensenumber":"lic1235","state":"Florida"}
{"licenseexpdate":"","licensenumber":"gsdgfsef" ," state":"South carolina"}
then this formula will do:

Code:

=MID(A2,FIND("state",A2)+8,FIND("}",A2)-FIND("state",A2)-9)

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