How can I Capitalize each word and remove all special characters and then combine all words in Excel-CodePudding

I have an Excel sheet with 500 values in column A. I am looking for a formula to Capitalize each word and remove all special characters and then combine all words.

Text in Column A: Vision, mission, values/text and approach

Desired Output: VisionMissionValuestTextAndApproach

CodePudding user response：

Use REDUCE and LAMBDA and SUBSTITUTE:

=SUBSTITUTE(PROPER(REDUCE(A1,{",","/"},LAMBDA(a,b,SUBSTITUTE(a,b," "))))," ","")

Adding to the array any other characters needing removal.

CodePudding user response：

For Office 365 Insiders:

=LET(ζ,{",","/"},SUBSTITUTE(CONCAT(PROPER(TEXTSPLIT(A1,ζ)))," ",""))

The part

{",","/"}

should be defined so as to contain all special characters which could occur within the string. It can of course be replaced with a reference to a range of worksheet cells containing those characters.

Note that, if you are not using an English-language version of Excel, the separator within the above array constant may be different from the comma.