1

I'm trying to do this without code.

I have an array of 4 columns with an undefined amount of rows, and I want to merge them into 1 column, but not by appending columns (so I can't use {A1:A;B1:C1;C;..}), but rather by appending cell, line by line.

Example:

Input:

Val1a Val1b Val1c Val1d
Val2a Val2b Val2c Val2d
Val3a Val3b Val3c Val3d
Val4a Val4b Val4c Val4d
Val5a Val5b Val5c Val5d
...

Output:

Val1a
Val1b
Val1c
Val1d
Val2a
Val2b
Val2c
Val2d
Val3a
...
2

Julien, let's say your sample data above were in the range A1:D5. Use this:

=TRANSPOSE(SPLIT(TEXTJOIN("/",TRUE,A1:D5),"/"))

This assumes that your data itself does not contain a forward slash, which in the formula, is being used as a temporary delimiter. If, for some reason, your data does contain a slash (e.g., URLs, etc.), just replace the two instances of the forward slash in the formula with any symbol or combination of symbols that your data does not include.

How it works:

  1. TEXTJOIN will string together the whole range into one long text string, running left to right per row, separating everything by a delimiter of choice.

  2. SPLIT will then split that text string again at those delimiters, which will make a long horizontal array of everything in the original range.

  3. TRANSPOSE will take that long horizontal array and change it to a vertical array.

If you then want to keep the hard data created by the formula:

  1. Select the results range.

  2. Hit Ctrl-C to copy it to clipboard.

  3. With the range still selected, hit Ctrl-Alt-V to Paste Special.

  4. A small clipboard icon will appear at the lower right corner of the range. Click it and choose "Paste values only."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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