Forum Discussion

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

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

       

      • Duffbeer's avatar
        Duffbeer
        Copper Contributor
        Jan 29, 2022
        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

Resources