Google Sheets – Split Text with Delimiter Containing Spaces

google sheets

I have a column (A) in Google Sheets where each row contains the following string:

"TEXT on MORE_TEXT" or, for more specific example "occurred on December 17, 2020". The "MORE_TEXT" part can be any of the 12 months.

I need to split it so that the "December 17, 2020" is in a separate column and my desired delimiter is " on ", which is a space character followed by the word "on" and then another space.

But when I use =SPLIT(A:A," on ") it ignores the spaces around "on" and I have no other ideas.

I am sure I am about to learn something simple but effective and am looking forward to it.

Best Answer

There are many ways to achieve the result you are looking. Here is one of the oldies, using RIGHT, LEN and FIND

=ArrayFormula(RIGHT(A:A,LEN(A:A) - FIND(" on ",A:A) - 3)

Another way, in this case using SPLIT, SUBSTITUTE and INDEX

=ArrayFormula(INDEX(SPLIT(SUBSTITUTE(A:A," on ","|"),"|"),1,2))

One way using regular expressions

=ArrayFormula(REGEXEXTRACT(A:A," on (.+)"))

P.S. The simplest way to avoid having errors for blanks cells below the las row having a value on A, delete all the bottom blank rows.

Formula

=ARRAYFORMULA(
   SPLIT(                        // string
     REGEXREPLACE(
       A1:A4,                    // text
       "[users,<=>compani]",     // regular_expression
       ""                        // replacement
     ),                          
   " ")                          // delimiter
 )

copy/paste
=ARRAYFORMULA(SPLIT(REGEXREPLACE(A1:A4, "[users,<=>compani]",""), " "))

Screenshot

enter image description here

Explained

The regular_expression is cleaning up the string, in preparation for the SPLIT. The replacement is simply set to contain an empty string. The result, yields a string that contains one or two values, separated by a white space. The delimiter in the SPLIT is set accordingly and all is put into an ARRAYFORMULA.

Google Sheets – How to Automatically Split Text to Column Using Google Apps Script

You don't need a script for this. Enter the following in C1:

=arrayformula(iferror(regexextract(B1:B, "ID#\d+")))

This will populate column C with Id numbers (where the data in B is available), leaving the rest blank. As more entries are submitted through the form, more Id numbers will be in column C.

Another version uses a second regexextract to drop the ID# part and keep only the digits:

 =arrayformula(iferror(regexextract(regexextract(B1:B, "ID#\d+"), "\d+")))

Best Answer

Related Solutions

Google-sheets – Split text with Google Sheets

Formula

Screenshot

Explained

Google Sheets – How to Automatically Split Text to Column Using Google Apps Script

Related Topic