Extract characters from the String

[soldat452002]

Hello

I attached a sample file. How can I only extract the States from the strings?

For example for CASE 29672 I only want to see Ohio. Thanks


CASE States Selected
29672 [{"licenseexpdate":"08/31/2019","licensenumber":"602228","state":"Ohio"}]
30883 [{"licenseexpdate":"01/01/2200","licensenumber":"194591","state":"South carolina"}]


30531 [{"licenseexpdate":"01/01/2200","licensenumber":"194591","state":"South carolina"}]
30378 [{"licenseexpdate":"10/31/2020","licensenumber":" 866913","state":"Pennsylvania"}]
30341 [{"licenseexpdate":"04/30/2020","licensenumber":"18581548","state":"New mexico"},{"licenseexpdate":"04/18/2019","licensenumber":"2238691","state":"Texas"}]

[June7]

Let x represent your field. Consider, after:

Mid(x, InStrRev(x,":")+2, InStrRev(x,"""")-InStrRev(x,":")-2)

However, your last example throws a big wrench into calc. Can there be more than two licenses?

Better build a VBA function to parse data.

[Micron]

what do you want to see for 30341?
EDIT - and does your record/string really hold all of those double quotes (")?

[Micron]

I see now from your sheet that indeed the data seems to contain many double quotes and some multiple license values in records.
AFAIK, there is no way to run either a UDF (user defined function) or built in function on that data. Quite certain that any call will choke because of the spaces and characters between the quotes. A function may handle "Black""White""Green" but I don't think it can be called for "Black":"White":"Green" or anything similar.

Likely you would have to run a query using the Replace function and remove all the double quotes first. Whether you do this against source data, or copy the data into another table would be up to you. Only then could you use any function like Instr or Replace, or any UDF against the record. Have to wonder though, what good is New Mexico Texas (or maybe New Mexico;Texas) as a pair of values that would be returned?

[isladogs]

The data is in JSON format and will need to be parsed.
Do you have the original JSON file?

You can use PowerQuery in Excel or in Access use my JATFA app which you can obtain from http://www.mendipdatasystems.co.uk/j...orm/4594138310
Otherwise, suggest you use the Split function

[June7]

I imported data into Access table. Consider:

Function ParseData(strS As String) As String
Dim aryS As Variant, i As Integer, strR As String, strSout As String
aryS = Split(strS, ",{")
For i = 0 To UBound(aryS)
    strR = Replace(Replace(Replace(aryS(i), """", ""), "]", ""), "[", "")
    strSout = Mid(strR, InStrRev(strR, ":") + 1, InStrRev(strR, "}") - InStrRev(strR, ":") - 1) & IIf(i >= 1, ",", "") & strSout
Next
ParseData = strSout
End Function

[davegri]

See attached:
DB links to the spreadsheet and calls it tblCase.
A new table, tblCaseState is included. If tblCase contains 2 entries in the same row, code creates 2 rows in tblCaseState with the same case no.
A new field in tblCaseState contains the state name. Thanks to Allen Browne for the parser function.

soldat-davegri-v01.zip

[Micron]

I wonder why all the complicated code in the thread or sample db, or tools when

UPDATE tblLicenses SET tblLicenses.[CENT OB States Selected] = Replace([CENT OB States Selected],"""","");

will remove all the double quotes. The other characters should not be a problem for any function - the function only has to find state or state: and move on from there. Or am I overlooking something?

The sample db is missing the linked table, so if anyone downloads it, you will need the zip Excel file from a previous post, rename it to suit then link to it.

[davegri]

Or am I overlooking something?

Probably not from what the OP asked. I just try to provide something extra for the OP to consider and possibly find salient.

[isladogs]

Similarly I suggested parsing as a JSON string so it could be generalised in case more complex examples arose with multiple states

[Micron]

Probably not from what the OP asked. I just try to provide something extra for the OP to consider and possibly find salient.

As do I, and brownie points for all of us who do. I admit that more than once I've taken the complicated road when someone else has a much more succinct approach. This time, I was thinking you guys were complicating the problem about the double quotes. Try as I might, I couldn't construct any way to pass that string to a function, but I knew a query could handle it no problem, which is why I didn't/don't see the point of worrying about all the other characters.

I took it upon myself to learn how to create my own version of a recursive function that's unlike the commonly found example regarding finding files in nested subfolders. What I ended up with might seem more convoluted/complicated than the code I saw for splitting multiples into rows and removing characters, but that was pretty intense too. However, the goal was for me to learn more about recursive functions on my own, which I did. I'll post something after I add notes and do further testing. I want to see how it handles my logic for an input string that is missing the key identifier "state" and identifies the case number at the same time.

[Micron]

First function will be pretty obvious to most. It simply creates a recordset and passes each of the 2 fields to a function that parses the state. Here's where it gets interesting. The 2nd function calls itself if it finds 1 state value. If it doesn't find another, it returns the state to the calling function (1st). If it does, it goes looking for another and appends subsequent states to the variable which becomes the concatenated return value of the function. The tricky parts were figuring out where to put some of the variables, how/when to update them, and ensuring subsequent passes of the 2nd function didn't mess things up. If you're an expert at recursive functions, maybe don't waste your time looking as you probably could have done better. As I said, it was a learning exercise for me, not an example of how I turned something complicated into simple, because I doubt I did.

db contains data modified by sql per my prior post (but no sql/query), a copy of the unedited data and a target table for results. A form will open that will allow you to run the function or clear the target table after you run it once. It is very fast on my laptop.

soldatMicron.accdb

[davegri]

OP seems lost in the shuffle. I hope our comments/code examples have helped.