0

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.

Improve this question
0

2 Answers 2

Reset to default
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)&"\|([^|]*)")))

Improve this answer
1
  • Thanks for the help with this one. It did work, much appreciated!
    – Mr. B
    Aug 9, 2016 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.

Improve this answer
3
  • 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, 2016 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, 2016 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, 2016 at 14:36

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