Split a column of strings with a single formula returning an array

Question

I have a column of strings like this:

+---------+
| a bb    |
| ccc d   |
| ee ffff |
+---------+

(exactly one space in each string), which I'd like to split by the space into two columns:

+-----+------+
| a   | bb   |
| ccc | d    |
| ee  | ffff |
+-----+------+

This can be done by putting B1 =split(A1," ") and dragging down the column. But I'm looking for an arrayformula version of this.

Unsuccessful attempt: the formula =arrayformula(split(A1:A3," ")) throws #VALUE error: "Function SPLIT parameter 1 value should be non-empty."

Answer 1

A variation of Normal's formula using only one regexextract (assuming data in col A, starting in row 2)

=ArrayFormula(if(len(A2:A), regexextract(A2:A, {"^[^ ]+","[^ ]+$"}),))

Answer 2

Google introduced a built-in tool for this purpose: Split text to columns.

1. Select a range to split, keeping in mind the separator menu will be at the bottom of this range (this is not very convenient for splitting a very tall column).
2. Use "Split column to text" from the menu
3. Select a separator.
4. Warning: the cells to the right of the column you are splitting may be overwritten when the column splits.

Answer 3

Here is a regex-based formula similar to Rubén's answer:

=arrayformula({regexextract(A1:A3,"^[^ ]+"), regexextract(A1:A3,"[^ ]+$")})

Here, ^[^ ]+ means: all non-space characters at the beginning of string, and [^ ]+$ means: all non-space characters at the end of string. One can replace space by comma, etc: for example, [^,]

This is only meant for strings containing exactly one separator: otherwise, the output will be wrong.

Custom function

To handle splitting into an arbitrary number of strings, I wrote a custom function which can be used as arraysplit(A1:A3, " "). It accepts column ranges (or single cells) as the first parameter, and separator as the second parameter.

Unlike the built-in split function, this one does not ignore empty strings: e.g., a,b,,c split by comma becomes four cells, the third of them blank. (In my opinion, ignoring empty strings is a major flaw in the design of the built-in split function.)

/**
 * Splits a column of strings by a separator.
 *
 * @param {A1:A3} range    Column range or a single cell
 * @param {" "} separator  Separating character or substring
 * @returns                Substrings, including empty strings
 * @customfunction
 */
function arraysplit(range, separator) {
  if (range.constructor !== Array) {
    range = [[range]];    // handle single-cell input
  }
  if (range[0].length !== 1) {
    throw new Error('First argument must be a column range');
  }
  var i, j, split, output = [], width = 1;
  for (i = 0; i < range.length; i++) {
    split = range[i][0].split(separator);
    output.push(split);  
    width = Math.max(width, split.length);   // max number of pieces 
  }
  for (i = 0; i < output.length; i++) {
    for (j = 0; j < width - output[i].length; j++) {
      output[i].push('');   // empty string to make rectangular array
    }
  }
  return output;
}

Bonus feature: unlike the built-in split, arraysplit accepts empty string as a separator, in which case it splits the strings into separate characters. (Of course, this behavior is simply inherited from the JavaScript's split method.)

Answer 4

Short answer

=ArrayFormula(
   {
      Left(A1:A3,FIND(" ",A1:A3)-SIGN(ROW(A1:A3))),
      Right(A1:A3,Len(A1:A3)-Find(" ",A1:A3))
    }
 )

Explanation

Looks that SPLIT() doesn't "like" having arrays as input parameters but LEFT(),RIGHT(),FIND() and LEN() work well with them.

An array with two columns is used.
The first column is obtained with LEFT().
The second column is obtained with RIGHT().
FIND() is used to find the position of the column separator. As we don't want the separator as part of the first column, we require an array to subtract 1 to each row (SIGN(ROW(A1:A3))).
LEN()-FIND() give us the number of characters to be taken by RIGHT().

Answer 5

Try following:

=arrayformula({split(A1:A3," ")})

It works for me.