Select Specific Text In Cell - Google Sheets

1
votes

I'm looking to extract a specific section of text from a cell.

The cell content looks like this; "modified":"1595515574184000","owner_id":"xxxx","priority_id":"xxx","severity_id":"xxx","status_id":"xxxx","type_id":"xxxx","user_email":"xxx","user_id":"xxxx","verifier_id":"xxxxx"

All the content is presented in this format however, the items are not always in the same order so functions such as left(), right() & mid() will not work for me.

I've also tried using the split() function, but it produces an array of items in different columns

In the above cell I'd like to extract the 16 digits after every "modified" tag.

If anyone has any suggestions or feedback let me know!

Thanks

2
google-sheetsgoogle-sheets-formula

2 Answers

1
votes

I'd like to add on to player0's answer above (which is already correct). I always prefer on always using specific number of repetition instead of using + and * when we are already sure of the length of the wanted string we want to extract. This way, we can always have the exact answer we are looking for and it will also act as data checker since it will error out if it will not follow the strict pattern "modified":"<16 digit>". I'd use this:

=REGEXEXTRACT(A1, """modified"":""(\d{16})""")

If you do not want it to be strict, then player0's 2nd answer should be great.

1
votes

try:

=REGEXEXTRACT(A1, "\d+")

enter image description here

or:

=REGEXEXTRACT(A1, "modified...(\d+)")

enter image description here