I have multiple columns filled with semicolon delimited strings.

I need to split the strings on these columns into rows. The columns to be split in my example data are

B, C, D

In my actual data, these are columns.

X, Y, AA, AB with a last column of AZ

The column header are the actual column headers.

I can do this with one column but not with multiple columns.

Thank you for your assistance

A Google sheet with data

References

• Array.forEach()
• String.split()

CodePudding user response：

To do that with a spreadsheet formula, use query() and flatten(), like this:

=arrayformula( 
  lambda( 
    authors, rawAffiliations, correspondents, authorAffiliations, 
    query( 
      { 
        From!A1:F1; 
        flatten(iferror(authors/0, From!A2:A)), 
        flatten(authors), 
        flatten(rawAffiliations), 
        flatten(correspondents), 
        flatten(authorAffiliations), 
        flatten(iferror(authors/0, From!F2:F)) 
      }, 
      "where Col1 is not null and Col2 is not null", 1 
   ) 
  )( 
    trim(split(From!B2:B, ";")), 
    trim(split(From!C2:C, ";")), 
    trim(split(From!D2:D, ";")), 
    trim(split(From!E2:E, ";")) 
  )
)

See your sample spreadsheet.