Formula to split JSON inside a Google Sheet cell into multiple rows

0
votes

I have the below JSON in a Google Sheet cell that I would like to split into multiple rows. Can anyone suggest a way to do this via a formula?

[{"links":"","links-href":"https://twitter.com/"},{"links":"","links-href":"https://cnn.com"}]

Specifically I would like the "links-href" split into different rows. I.e.

https://twitter.com/
https://cnn.com

Should be in 2 separate rows. Thanks for any and all help

1
arraysjsongoogle-sheetsgoogle-sheets-formula

1 Answers

1
votes

I think the next formula can help you

=ArrayFormula(REGEXEXTRACT(transpose(SPLIT(K1,",{",FALSE,TRUE)),"links-href"":""(.*?)""}"))

enter image description here