#1
|
|||
|
|||
Extract States from a String
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"}] |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
That is perfect. How can I append the values to a excel spreadsheet
|
#4
|
|||
|
|||
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 ??? |
#5
|
|||
|
|||
Quote:
|
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
Okay thanks.
Im assuming I need to change this to my spreadsheet location. MsgBox arr(UBound(arr)) |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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 .
|
#10
|
|||
|
|||
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 |