Split Cell data into lookup column

[Ramaha87]

Hi all,

need help on below request please,

Available cell data: 

[{"qid":"1","status":"GREAT"},{"qid":"2","status":"GREAT"},{"qid":"3","status":"GREAT"},{"qid":"4","status":"GREAT"}]

Output required from above cell:

qid-1	qid-2	qid-3	qid-4
Great	Great	Great	Great

is it possible to split data as per lookup ?

Thanks in advance.

[#2]

@Ramaha87 

=LET(
remove_chars;SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"""status"":",""),"{",""),"}",""),"""",""),":","-"),
length,LEN(remove_chars),
remove_brackets,MID(remove_chars;2;length-2),
result,WRAPCOLS(TEXTSPLIT(remove_brackets,,","),2),
result)

[#3]

@Ramaha87 

Try this one:

=LET(
    split, TEXTSPLIT(A1, , """"),
    k, INT(COUNTA(split) / 8),
    seq, SEQUENCE(k, , 2, 😎 + {0, 2, 6},
    matrix, TRANSPOSE(INDEX(split, seq)),
    QID, TAKE(matrix, 1) & "-" & INDEX(matrix, 2),
    status, TAKE(matrix, -1),
    VSTACK(QID, status)
)

[#4]

Hi @Patrick2788 

Thanks for the replay but it is not working for me or may be i am doing something wrong please help me with this?

as i have pasted your given formula at H2 please refer below,

[#5]

@Ramaha87 

What Excel version do you use?

[#6]

It appears you may be using a version of Excel that does not support dynamic arrays.

[#7]

=WEBSERVICE("https://e.anyoupin.cn/eh3/?preg_match_all_join~qid"":""(\d)""~" & A2 & "~1")

=WEBSERVICE("https://e.anyoupin.cn/eh3/?preg_match_all_join~status"":""(\w+)"~" & A2 & "~1")

[#8]

@Ramaha87 

Power Query could work transforming to

, whatever.

[#9]

@Ramaha87 

Shame the OP doesn't appear to use 365.

= LET(
    noQuote,  SUBSTITUTE(data, """", ""),
    dataItem, TEXTSPLIT(noQuote,{"[","{","},{","}","]"},,TRUE),
    firstRow, SUBSTITUTE(TEXTBEFORE(dataItem, ","), ":", "-"),
    status,   TEXTAFTER(dataItem, ":", 2),
    VSTACK(firstRow, status)
  )

It seems that 365 has all the 'good' functions!