8

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."

4
  • I don't get and error for =arrayformula(split(A1:A3," ")) but it doesn't work for me as desired anyway.
    – Rubén
    Jan 10, 2016 at 5:44
  • @Rubén Are you using Old Sheets for this? Mine is New. In this answer Jacob used arrayformula-split, and it was in an old sheet.
    – user79865
    Jan 10, 2016 at 5:47
  • I'm using the new Google Sheets too.
    – Rubén
    Jan 10, 2016 at 5:49
  • For an alternative formula using regex and being able to handle multiple columns, see stackoverflow.com/a/30202802/1595451
    – Rubén
    Jan 10, 2016 at 6:58

5 Answers 5

3

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, {"^[^ ]+","[^ ]+$"}),))
3
  • 1
    For the record: to split by another character, such as a comma, one would replace [^ ] with [^,] in two places.
    – user79865
    Mar 9, 2016 at 22:11
  • 1
    There is a side effect of using one regexextract to get both pieces: when splitting strings like "abc 123", the second result ends up being text rather than a number. The long version {regexextract(A2:A, "^[^ ]+"), regexextract(A2:A,"[^ ]+$")} makes 123 a number.
    – user79865
    Mar 14, 2016 at 20:21
  • @404 justed tested. It doesn't (not for me at least). :-/ Shouldn't come as a surprise though: regex operates on text. If you wanted to convert the second part to a number you'd need something like: =arrayformula({regexextract(A2:A3;"^[^ ]+")\ regexextract(A2:A3;"[^ ]+$")+0})
    – JPV
    Mar 15, 2016 at 9:35
3

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.

separator

2

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.)

2
  • Your function could return the error message as a custom error message instead of returning a result by using throw. See mogsdad.wordpress.com/2015/07/08/…
    – Rubén
    Jan 10, 2016 at 6:39
  • 1
    That's good to know; included.
    – user79865
    Jan 10, 2016 at 6:58
2

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().

1

Try following:

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

It works for me.

1
  • There is no need to use curly braces on this formula.
    – Rubén
    Oct 12, 2019 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.