0

I have content like "3 4 5; 5 6 7; 8 9 10" in a cell and I would like to split it by the semicolons so that I have "3 4 5", "5 6 7", and "8 9 10" in three cells.

SPLIT() normally does this fine, but the problem is that Google Sheets keeps on interpreting the content as a date. Selecting "Plain Text" doesn't help because it converts the content to a date, then converts that date to a number, then displays the number as Plain Text.

Example Sheet

enter image description here

1

2 Answers 2

1

If the number of groups to extract is always the same, you can use regexextract(), like this:

=regexextract(A1, rept("([\d ]+)[^\d]*", 3))

To process all of column A1:A, use an array formula, like this:

=arrayformula( 
  iferror( 
    regexextract(A1:A, rept("([\d ]+)[^\d]*", 3)) 
  ) 
)

If the number of groups varies, use split(). You can evaluate it over an array like this:

=arrayformula( 
  split( 
    regexreplace(A1:A, "^|; ", "$0'"), 
    "; ", 
    false 
  ) 
)

If you cannot use split() for some reason, use the regexextract() formula but replace the 3, which is the number_of_repetitions parameter to rept(), with a function that calculates the number of separators in the data and adds one.

0

I eventually found a workaround:

=SPLIT(CONCAT("'",REGEXREPLACE(A5,"\; ","; '")),"; ",false)

The idea is to add a quote mark at the beginning and after each delimiter to the string, and then invoking SPLIT. This feels very kludgey so I'm still hoping for a simpler solution.

1
  • I'm still hoping for a simpler solution. Aren't the answers by @doubleunary simpler? Plus they have the advantage of processing an entire column of values rather than just one row.
    – Tedinoz
    Dec 26, 2021 at 1:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.