0

I have a column of strings containing data split by semicolons.

Example Strings
game1;video1;photo1
game2;video2;
event1;recape1;videoe1;photoe1

I would like to convert it into one column containing:

Example Results
game1
video1
Photo
game2
video2
event1
recape1
videoe1
photoe1

I'm thinking of using SPLIT, then FLATTEN, and finally FILTER to remove the empty ones. But that means I have to use up N columns just for the transition.

Is it possible to pack it into one formula so I have one input column and one output column?

1

2 Answers 2

1

Wrap flatten(split()) in a query(), like this:

=arrayformula( 
  query( 
    flatten(iferror(split(A2:A; ";"))); 
    "where Col1 is not null"; 0 
  ) 
)
1

TOCOL

Google Sheets has now added the TOCOL function which transforms an array or range into a single column.

Using TOCOL instead of FLATTEN greatly reduces the length of this formula because it can ignore blanks and errors.

=ARRAYFORMULA(
   TOCOL(
      SPLIT(A:A,";"),3)) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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