-1

I have a huge data file. Each line may contain one or more entries separated by a pipe:

one|two|three
alpha
uno|dos
beta
gamma

I can use "Text to Columns" to remove the | and put each entry in its own column:

one     two     three
alpha
uno     dos
beta
gamma

But my desired result is to have all items in a single column:

one
alpha
uno
beta
gamma
two
three
dos

How can I easily get all entries into a single column? (order does NOT matter)

player0
  • 124,011
  • 12
  • 67
  • 124
BizzyBob
  • 12,309
  • 4
  • 27
  • 51

2 Answers2

2

try:

=ARRAYFORMULA(QUERY(FLATTEN(IFERROR(SPLIT(FLATTEN(A:Z), "|"))), "where Col1 is not null"))
player0
  • 124,011
  • 12
  • 67
  • 124
  • it seems you're not worried about warning folks that there's something weird about Flatten() in that it's undocumented? i feel like we should warn people that it could get deprecated, no? – MattKing Jul 18 '20 at 19:54
  • well, if ggl dudes will remove it, we still have query/transpose/query/transpose combo :) tho that is undocumented as well – player0 Jul 18 '20 at 20:22
  • well we will!, but not the folks we've helped with flatten()... – MattKing Jul 20 '20 at 18:33
0

Assume Range A1:A5 is your data

=TRANSPOSE(SPLIT(JOIN("|",A1:A5),"|"))

JOIN them to add "|" to all of them first

then SPLIT text that has "|" to be 1 row, many columns

then TRANSPOSE to get just one column going

This is the basic one I can think of, anyways there are a lot of ways to do this

zummon
  • 906
  • 3
  • 9
  • 27
  • If it is a huge data file. this formula may hit the 50,000 character limit of the JOIN function. – kirkg13 Jul 26 '20 at 21:42