Split Text By every Nth Delimiter and Transpose Google Sheets

Question

I need to extract text from a block of text. The block looks like this:

{"some text"},{"some more text"},{"some other"},{"some other text"},{"let's add more"},{"even more other text"}

The text is supposed to be in pairs so that the first and second {""},{""} go into the same cell and so forth. I tried the split function but since the delimiter is a comma I am not able to split in pairs. So I need to split after every 2 commas and put the resulting pair in it's own cell. Then Transpose the result so that the text goes inside a column vertically.

I tried =SPLIT("",",") but didn't get the desired output.

I am using Google sheets for this and would like to do this inside Google Sheets, but I'm open to doing this inside Excel.

If you want to have a look at the Google Sheets File, here is the link https://docs.google.com/spreadsheets/d/1sbK6nF-6SO4oOKTdercgnfHGeQol-vfV5SRcFXNBy5o/edit?usp=sharing

user_9758194 · Answer

Interesting and fun query.

In B2, see if the following formula works for you:

=TRANSPOSE(SPLIT(REGEXREPLACE(A2,"([^,]+,[^,]+),","$1|"),"|"))

The regular expression can be made more specific to target only those comma's in between curly brackets if need be.

Edit

To SPLIT your desired outcome into two columns and get rid of the curly brackets and outer quotes I came up with:

=TRANSPOSE({SPLIT(REGEXREPLACE(A2,"{""([^,]+)""},{""([^,]+)""}(?:,|$)","$1|"),"|");SPLIT(REGEXREPLACE(A2,"{""([^,]+)""},{""([^,]+)""}(?:,|$)","$2|"),"|")})

user_279806 · Answer

To adapt the solution for ARRAYFORMULA we just need to join all the strings with comma.

A few notes on the following solution:

With REGEXREPLACE we strip all the surrounding {"..."}. At the same time we replace every even comma (not just every comma, but the ones between records, e.g. commas inside the strings won't be affected) with "♠" for later splitting pairs into the separate strings, and every odd comma we replace with "♥" for later splitting a pair into two strings.

=ARRAYFORMULA(
  SPLIT(
    TRANSPOSE(
      SPLIT(
        REGEXREPLACE(
          TEXTJOIN(
            ",",
            True,
            A2:A
          ),
          "\{""(.*?)""\},\{""(.*?)""\},?",
          "$1♥$2♠"
        ),
        "♠"
      )
    ),
    "♥"
  )
)

Split Text By every Nth Delimiter and Transpose Google Sheets

2 Answers