Split challenges on an array formula

Question

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.

Use code fences to format your code. Test formatting before posting questions. — TheMaster
– TheMaster, Commented Dec 16, 2021 at 15:09

TheMaster · Accepted Answer · 2021-12-16 15:12:42Z

1

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.

answered Dec 16, 2021 at 15:12

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

player0 · Accepted Answer · 2021-12-16 16:05:54Z

0

use:

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

or:

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

edited Dec 16, 2021 at 16:05

answered Dec 16, 2021 at 15:54

player0

131k14 gold badges92 silver badges149 bronze badges

2 Comments

player0 Over a year ago

@Jerome answer updated

TheMaster Over a year ago

While it works here, I don't think json can or should be parsed like this. Plenty of things could go wrong. Just for eg, If Name is Tania, Dewes, it'll fail.

Collectives™ on Stack Overflow

Split challenges on an array formula

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related