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 |
Try this out:
=ARRAYFORMULA(
IFERROR(
SPLIT(REGEXREPLACE(A2:A, "\d{4}|Corporate", ), " ")
)
)