0

This question already has an answer here:

I'm trying to figure out "how to" split one Google Sheets cell into multiple cells. I'm using the "|" character to identify where the string in one cell should will be split. Ideas?

I have the following in cell B1. space|space | will always separate values in the single cell.

Chromebook 101 | GHS | 8:00 am | ID#100103

I need to split the data into FOUR parts and into FOUR different cells.

  • H1 = Chromebook 101
  • I1 = GHS
  • J1 = 8:00 am
  • K1 = ID#100103

The data is being added to Google Sheets via form input and I need this to update automatically. I assume an arrayformula will be needed.

If possible, I'd also like to drop the | from cells H1, I1, J1, and K1.

marked as duplicate by user79865, ale, serenesat, jonsca Aug 9 '16 at 22:21

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

After a search I found at a 2014 StackOverflow post that: ARRAYFORMULA() does not work with SPLIT()

From another post though (Fix for ARRAYFORMULA() SPLIT()) and by simple replacements to fit your question this will work for you:

=ARRAYFORMULA(IFERROR(REGEXEXTRACT("|"&REGEXREPLACE(B1:B100,"\n","|"),"^"&REPT("\|[^|]*",COLUMN(OFFSET(A1,,,1,4))-1)&"\|([^|]*)")))

  • Thanks for the help with this one. It did work, much appreciated! – Mr. B Aug 9 '16 at 14:56
1

There is a command split for exactly this purpose:

= split(B1, " | ", false)

where the second argument is the separator; and since we only want to separate by this whole group, the 3rd argument is needed to say "don't split by each of these characters". Including spaces in the separator eliminates them from the output, making it neater.

If you have variable number of spaces around separators, the trim command is useful:

= arrayformula(trim(split(B1, "|")))

will also separate by |, without expecting spaces around it, yet any such spaces will be dropped.

  • RedRedWine, Thanks as always. I've tried adding = arrayformula(trim(split(F2, "|"))) and = arrayformula(trim(split(F:F, "|"))) but neither auto populate down. Do you see what I'm missing? Thanks! docs.google.com/spreadsheets/d/… – Mr. B Aug 9 '16 at 14:19
  • 3
    You asked about splitting one cell, and then move the goal posts to splitting every cell in a column. This is not a routine change. Please be specific about what you are after from the beginning. Your new question is addressed in Split a column of strings with a single formula returning an array – user79865 Aug 9 '16 at 14:24
  • RedRedWine, I'm sorry my question/comment wasn't written written correctly and changed as I developed the document. As always, I appreciate all of your help on here. You've saved me HUNDREDS of hours with your help. Have a great day! – Mr. B Aug 9 '16 at 14:36

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