Forum Discussion
Solved
WHOLE UC word split
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
12 Replies
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,""))- 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,"")),"#","-"))- Are you referring to #NAME? error?
What version of Excel are you using?
