Jan 29 2022 06:13 AM
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
Jan 29 2022 06:45 AM
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,""))
Jan 29 2022 08:28 AM
Jan 29 2022 10:29 AM
Jan 29 2022 10:49 AM
Jan 29 2022 10:57 AM
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"),"")),"#","-")
Jan 29 2022 11:05 AM
Jan 29 2022 11:19 AM
Jan 29 2022 12:55 PM
Jan 30 2022 01:44 AM
SolutionFeb 04 2022 08:16 AM
Feb 04 2022 08:18 AM
Jan 30 2022 01:44 AM
Solution