SOLVED

WHOLE UC word split

%3CLINGO-SUB%20id%3D%22lingo-sub-3085861%22%20slang%3D%22en-US%22%3EWHOLE%20UC%20word%20split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3085861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EWondered%20if%20anyone%20might%20assist%20or%20point%20me%20in%20the%20right%20direction.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20need%20to%20split%20text%20by%20(UPPERCASE%20WORDS%20%26amp%3B%20LOWERCASE%20WORDS%20not%20UC%20characters)%2C%20for%20example%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFrom%20%E2%80%9C16-04%20CRICKET%20World%20Cup%E2%80%9D.%20Too%20%E2%80%9C16-04%20CRICKET%E2%80%9D%2C%20%E2%80%9CWorld%20Cup%E2%80%9D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFrom%20%E2%80%9C23-10%20AMERICAN%20FOOTBALL%20Playoffs%E2%80%9D.%20%26nbsp%3BToo%20%E2%80%9C23-10%20AMERICAN%20FOOTBALL%E2%80%9D%2C%20%E2%80%9CPlayoffs%E2%80%9D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWithout%20VBA%20if%20possible%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3085861%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3086005%22%20slang%3D%22en-US%22%3ERe%3A%20WHOLE%20UC%20word%20split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3086005%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1290838%22%20target%3D%22_blank%22%3E%40Duffbeer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0Aa%2CFILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(SUBSTITUTE(A2%2C%22-%22%2C%22%23%22)%2C%22%20%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22)%2C%0ASUBSTITUTE(TEXTJOIN(%22%20%22%2CTRUE%2CIF(EXACT(UPPER(a)%2Ca)%2Ca%2C%22%22))%2C%22%23%22%2C%22-%22)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DTRIM(SUBSTITUTE(A2%2CB2%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3086396%22%20slang%3D%22en-US%22%3ERe%3A%20WHOLE%20UC%20word%20split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3086396%22%20slang%3D%22en-US%22%3EHi%2C%20thank's%20for%20your%20swift%20response.%20I%20am%20getteing.%20INVALID%20NAME%20ERROR.%3CBR%20%2F%3EI%20have%20pasted%20this%20%3DLET(a%2CFILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(SUBSTITUTE(A2%2C%22-%22%2C%22%23%22)%2C%22%20%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22)%2CSUBSTITUTE(TEXTJOIN(%22%20%22%2CTRUE%2CIF(EXACT(UPPER(a)%2Ca)%2Ca%2C%22%22))%2C%22%23%22%2C%22-%22))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3086884%22%20slang%3D%22en-US%22%3ERe%3A%20WHOLE%20UC%20word%20split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3086884%22%20slang%3D%22en-US%22%3EAre%20you%20referring%20to%20%23NAME%3F%20error%3F%3CBR%20%2F%3EWhat%20version%20of%20Excel%20are%20you%20using%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3086952%22%20slang%3D%22en-US%22%3ERe%3A%20WHOLE%20UC%20word%20split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3086952%22%20slang%3D%22en-US%22%3EYes%20referring%20to%20%23Name%20error.%20Can%20I%20not%20post%20a%20small%20dataset%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3086973%22%20slang%3D%22en-US%22%3ERe%3A%20WHOLE%20UC%20word%20split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3086973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1290838%22%20target%3D%22_blank%22%3E%40Duffbeer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20using%20an%20old%20version%20of%20Excel.%3C%2FP%3E%3CP%3EReplace%20the%20formula%20with%20this%20slightly%20longer%20one%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUBSTITUTE(TEXTJOIN(%22%20%22%2CTRUE%2CIF(EXACT(UPPER(%0AFILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(SUBSTITUTE(A2%2C%22-%22%2C%22%23%22)%2C%22%20%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22))%2C%0AFILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(SUBSTITUTE(A2%2C%22-%22%2C%22%23%22)%2C%22%20%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22))%2C%0AFILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(SUBSTITUTE(A2%2C%22-%22%2C%22%23%22)%2C%22%20%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22)%2C%22%22))%2C%22%23%22%2C%22-%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3087016%22%20slang%3D%22en-US%22%3ERe%3A%20WHOLE%20UC%20word%20split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3087016%22%20slang%3D%22en-US%22%3ESorry%20Detlef%20in%20my%20haste%20I%20did%20not%20read%20your%20second%20question.%20Using%20excel%202016.%20Pasted%20the%20above%20into%20cell%20B1%20as%20data%20starts%20in%20cell%20A1.%20Same%20error%20message.%20After%20pasting%20do%20I%20need%20to%20change%20any%20referencing.%20Garry%3C%2FLINGO-BODY%3E
Occasional Contributor

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

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

 

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?
Yes referring to #Name error. Can I not post a small dataset

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

 

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
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.
I will give it a go with helper cells. Need to start somewhere. Thank's for all your efforts. Garry

@Duffbeer 

See attached file.

 

best response confirmed by Duffbeer (Occasional Contributor)
Solution

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

Riny_van_Eekelen_0-1643535821821.png

 

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
Thank you Riny. Had no idea of this function. I will have to visit the library and investigate further. Many thanks, Garry