Google Sheets – How to Split a Column of Strings with a Single Formula

google sheets

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

Best Answer

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, {"^[^ ]+","[^ ]+$"}),))