CODEWITHSUNDEEP
regexgoogle-sheetssplitgoogle-sheets-formulasubstitution
Jerome
Jerome

Reputation: 77

Split challenges on an array formula

I am trying to split the following standard: This is column B

{"Dom":"DSQR","ID":"987","Name":"Stephen Jones"}
{"Dom":"DSQR","ID":"987","Name":"Tania Dewes"}

The formula is as follows:

=ArrayFormula(IF(B2:B="", "", TRIM(SPLIT(B2:B,M2))))

M2 cell is:

ID":"

Result is:
enter image description here

Could you explain why Stephen Jones shows correctly and Tania doesn't? I'd like the split to work like it does on the first line.

Upvotes: 1

Views: 73

Answers (2)

player0
player0

Reputation: 1

use:

=INDEX(IFERROR(REGEXREPLACE(SPLIT(A1:A, ":,", 1), "[\{\}""]", )))

enter image description here

or:

=INDEX(IFERROR(SPLIT(REGEXREPLACE(A1:A, "[\{\}""]|ID", ), ":,", 1)))

enter image description here

Upvotes: 0

TheMaster
TheMaster

Reputation: 50462

That's because there's none of ID":" in Stephen Jones, while there's a D in Tania Dewes. Suggest using a proper parser, like a custom function to parse JSON.

Upvotes: 1

Related Questions