1

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
5
  • That's very helpful, thank you very much! I also use the IF(LEN) code to keep the blank rows empty in array formulas, so I modified your suggestion as below =ARRAYFORMULA(IF(LEN(A:A)>0,RIGHT(A:A,LEN(A:A) - FIND(" on ",A:A) - 3),"")) Thank you again!
    – YBB
    Apr 4, 2021 at 17:51
  • Bear in mind that returning "" still returning setting a value for all the blank cells in Column A. This might not be a problem is you keep your spreadsheet simple and your sheet with few blank rows.
    – Rubén ♦
    Apr 4, 2021 at 18:23
  • @M2Z by the way, please read What should I do when someone answers my question?
    – Rubén ♦
    Apr 4, 2021 at 20:26
  • 1
    Sometimes deleting multiple blank rows becomes time-consuming but I am happy to create a different question to find out how to avoid adding "" to keep the "false" and "error" values out. Also, I upvoted your answer but it won't show up as I am a newbie here and the "Accept" checkmark finally showed up after I reinstalled 2 browsers (unrelated issues) so I finally did that too
    – YBB
    Apr 4, 2021 at 22:49
  • @M2Z Thanks. Here is a link to question with an answer showing the use of ARRAY_CONSTRAIN together with COUNTIF webapps.stackexchange.com/q/99783/88163
    – Rubén ♦
    Apr 4, 2021 at 22:58

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.