Forum Discussion

Copper Contributor

Jan 29, 2022

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 “...

excel

Riny_van_Eekelen
Jan 30, 2022
Duffbeer Perhaps PowerQuery works for you. No complex formulae, no VBA. See attached.

duffbeer.xlsx17 KB

Detlef_Lewin

Silver Contributor

Jan 29, 2022

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,""))

Duffbeer
Copper Contributor
Jan 29, 2022
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,"")),"#","-"))
- Detlef_Lewin
  Silver Contributor
  Jan 29, 2022
  Are you referring to #NAME? error?
  What version of Excel are you using?
  - Duffbeer
    Copper Contributor
    Jan 29, 2022
    Yes referring to #Name error. Can I not post a small dataset