SPLIT using " " delimiter in Google Sheets won't always preserve period following number

Question

I'm using the SPLIT function to divide text around white spaces (" ") in strings. However, the output is inconsistent when a number is immediately followed by a period.

Column A below contains strings, and column B the function =SPLIT(A1," ") copied down:

Note how cell B1 does not contain a period after "2015". Is this a bug?

In the meantime, I'm using the following regular expression as a workaround:

=REGEXEXTRACT(A1,"(.+)\s(.+)")

However, is there a way to account for any number of white spaces in a string?

PS: I posted this in the Google Product Forum, and I'll be sure to update either thread if progress is made!

Answer 1

This may get a little 'ugly' but see if this works:

=ArrayFormula(iferror(REGEXEXTRACT(" "&A1:A,"^"&REPT("\s+[^\s]+",COLUMN(OFFSET(A1,,,1,6))-1)&"\s+([^\s]+)")))

• 6 in the offset denotes the (maximum) number of columns/groups that are outputted.
• as this is an arrayformula, no 'dragging down' is needed.