Split Cell data into lookup column

Copper Contributor

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-1qid-2qid-3qid-4
GreatGreatGreatGreat

 

is it possible to split data as per lookup ?

 

Thanks in advance.

8 Replies

@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)

 

@Ramaha87 

Try this one:

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

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,

Ramaha87_0-1710575799246.png

 

@Ramaha87 

What Excel version do you use?

 

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

=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")

@Ramaha87 

Power Query could work transforming to

image.png

, whatever.

@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!