Capitalise every second/third word

[parsnipsnatcher]

How do I capitalise every other word but have the rest normal?

Would look like this:

John Oscar WILLIAMS
Amelia Rose RICHARDS

or

The quick BROWN fox jumps OVER the lazy DOG.

Is that even possible at all?

 

[KOKOSEK]

As you are using XL2019 let do it with LET and FILTERXML:

Book1
	A	B
1	the quick brown fox jumps over the lazy dog	the quick BROWN fox jumps OVER the lazy DOG
2	john oscar williams	john oscar WILLIAMS
3	amelia rose richards	amelia rose RICHARDS
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET( WordsArray,FILTERXML("<OPEN><ELEMENT>"&SUBSTITUTE(A1," ","</ELEMENT><ELEMENT>")&"</ELEMENT></OPEN>","//ELEMENT"), ReCreate,IFERROR(IF(MOD(ROW($1:$20),3)=0,UPPER(WordsArray),WordsArray),""), TEXTJOIN(" ",1,ReCreate) )

if you want to change which word have to be capitalize change 3 in MOD function.

 

[Fluff]

@KOKOSEK the LET function doesn't exist in 2019 version.

 

[KOKOSEK]

I've checked FILTERXML but not LET...
Then

Book1
	A	B
1	the quick brown fox jumps over the lazy dog	the quick BROWN fox jumps OVER the lazy DOG
2	john oscar williams	john oscar WILLIAMS
3	amelia rose richards	amelia rose RICHARDS
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=TEXTJOIN(" ",1,IFERROR(IF(MOD(ROW($1:$20),3)=0,UPPER(FILTERXML("<OPEN><ELEMENT>"&SUBSTITUTE(A1," ","</ELEMENT><ELEMENT>")&"</ELEMENT></OPEN>","//ELEMENT")),FILTERXML("<OPEN><ELEMENT>"&SUBSTITUTE(A1," ","</ELEMENT><ELEMENT>")&"</ELEMENT></OPEN>","//ELEMENT")),""))

 

[parsnipsnatcher]

Thank you so much, this worked for me!

I've checked FILTERXML but not LET...
Then

Book1
	A	B
1	the quick brown fox jumps over the lazy dog	the quick BROWN fox jumps OVER the lazy DOG
2	john oscar williams	john oscar WILLIAMS
3	amelia rose richards	amelia rose RICHARDS
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=TEXTJOIN(" ",1,IFERROR(IF(MOD(ROW($1:$20),3)=0,UPPER(FILTERXML("<OPEN><ELEMENT>"&SUBSTITUTE(A1," ","</ELEMENT><ELEMENT>")&"</ELEMENT></OPEN>","//ELEMENT")),FILTERXML("<OPEN><ELEMENT>"&SUBSTITUTE(A1," ","</ELEMENT><ELEMENT>")&"</ELEMENT></OPEN>","//ELEMENT")),""))

 

[KOKOSEK]

Happy to help.
BTW: that's weird that MS add FILTERXML into O2019 but not LET (I did not check other exclusive O365 functions).

 

[Fluff]

The Filterxml function came out in 2013