WHOLE UC word split

[Duffbeer]

Wondered if anyone might assist or point me in the right direction.

I need to split text by (UPPERCASE WORDS & LOWERCASE WORDS not UC characters), for example:

From “16-04 CRICKET World Cup”. Too “16-04 CRICKET”, “World Cup”

From “23-10 AMERICAN FOOTBALL Playoffs”.  Too “23-10 AMERICAN FOOTBALL”, “Playoffs”

Without VBA if possible

[#2]

@Duffbeer 

Try this.

=LET(
a,FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(A2,"-","#")," ","</z><z>")&"</z></y>","//z"),
SUBSTITUTE(TEXTJOIN(" ",TRUE,IF(EXACT(UPPER(a),a),a,"")),"#","-")
)

=TRIM(SUBSTITUTE(A2,B2,""))

[#3]

Hi, thank's for your swift response. I am getteing. INVALID NAME ERROR.
I have pasted this =LET(a,FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(A2,"-","#")," ","</z><z>")&"</z></y>","//z"),SUBSTITUTE(TEXTJOIN(" ",TRUE,IF(EXACT(UPPER(a),a),a,"")),"#","-"))

[#4]

Are you referring to #NAME? error?
What version of Excel are you using?

[#5]

Yes referring to #Name error. Can I not post a small dataset

[#6]

@Duffbeer 

You are using an old version of Excel.

Replace the formula with this slightly longer one:

=SUBSTITUTE(TEXTJOIN(" ",TRUE,IF(EXACT(UPPER(
FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(A2,"-","#")," ","</z><z>")&"</z></y>","//z")),
FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(A2,"-","#")," ","</z><z>")&"</z></y>","//z")),
FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(A2,"-","#")," ","</z><z>")&"</z></y>","//z"),"")),"#","-")

[#7]

Sorry Detlef in my haste I did not read your second question. Using excel 2016. Pasted the above into cell B1 as data starts in cell A1. Same error message. After pasting do I need to change any referencing. Garry

[#8]

So that eliminates TEXTJOIN(). You need at least Excel 2019.
What remains are solutions with helper columns (depending on the number of blanks in your text) or VBA.

[#9]

I will give it a go with helper cells. Need to start somewhere. Thank's for all your efforts. Garry

[#10]

@Duffbeer 

See attached file.

[#11 · Accepted solution]

@Duffbeer Perhaps PowerQuery works for you. No complex formulae, no VBA. See attached.

[#12]

Thank you Detlef. I can see that you have put in a lot of effort. I am going with your response. Many thank's Garry

[#13]

Thank you Riny. Had no idea of this function. I will have to visit the library and investigate further. Many thanks, Garry