Split challenges on an array formula-CodePudding

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:

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.

CodePudding user response：

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.

CodePudding user response：

use:

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

or:

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