Search code examples
google-sheetsgoogle-sheets-formula

Split If not certain word and not first 4 characters


I have a column using the following structure:

YYYY Month Corporate Word1 Word2
YYYY Month Corporate Word1 Word2 Word3
YYYY Month Word1 Word2
YYYY Month Word1 Word2 Word3

I currently have a formula that splits each of the words in that string into separate cells.

=IFERROR(SPLIT(A2," "),"")

This is what I get now:

String Split Formula Split Result2 Split Result3 Split Result4 Split Result5 Split Result6
YYYY Month Corporate Word1 Word2 YYYY Month Corporate Word1 Word2
YYYY Month Corporate Word1 Word2 Word3 YYYY Month Corporate Word1 Word2 Word3
YYYY Month Word1 Word2 YYYY Month Word1 Word2
YYYY Month Word1 Word2 Word3 YYYY Month Word1 Word2 Word3

I would like to ignore the year and any mention of "Corporate". Would also appreciate a formula that populates for the whole column instead of having to copy down the same formula. When I try the following:

=ARRAY_CONSTRAIN(MAP(A2:A,LAMBDA(x,IFERROR(SPLIT(x," "),""))),COUNTA(A2:A),1)

It only returns the year. No other word is extracted.

This is what I would like the end result to be:

String Split Formula Split Result2 Split Result3 Split Result4
YYYY Month Corporate Word1 Word2 Month Word1 Word2
YYYY Month Corporate Word1 Word2 Word3 Month Word1 Word2 Word3
YYYY Month Word1 Word2 Month Word1 Word2
YYYY Month Word1 Word2 Word3 Month Word1 Word2 Word3

Solution

  • Try this out:

    =ARRAYFORMULA(
       IFERROR(
         SPLIT(REGEXREPLACE(A2:A, "\d{4}|Corporate", ), " ")
       )
     )