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

[asifakhtar]

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

 

[KRice]

You could try something like this:

Book3
	A	B
1
2	Vision, mission, values/text and approach	VisionMissionValuesTextAndApproach
3	Vision-mission & 7% ideas, lucky things: other stuff	VisionMission7IdeasLuckyThingsOtherStuff
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=SlugifyMod(SUBSTITUTE(PROPER(A2)," ",""),1)

This uses a modified version of the Slugify LAMBDA function:

SLUGIFY

SLUGIFY function converts the given string to a URL slug. We often use the document title as the page name to create a web page, which is called "URL slug". The document title often has invalid characters that cannot be used in the URL. Therefore, we generate a clean version of the title by...

www.mrexcel.com

...modified to permit capital letters (and the original version already allows for numbers and lower case letter), and then I eliminated the feature that inserts hyphens between words. The input to the function is preconditioned by coverting it to PROPER case and then deleting spaces. To run it, copy the LAMBDA function and go to Formula > Name Manager > New...then paste the code in the "Refers to" block and name the function SlugifyMod...and then save and exit. On your worksheet, the function is then called as shown in the example above. Copy the entire block of code and simply paste it into the "Refers to" field on the Name Manager.

=LAMBDA(reference,ndx,
      IF(ndx > LEN(reference),
          SUBSTITUTE(reference, "--", ""),
          SlugifyMod(
              LET(
                     character, MID(reference, ndx, 1),
                     charcode, CODE(character),
                     LEFT(reference, ndx - 1) &
                          IF(OR(AND(charcode > 96, charcode < 123), AND(charcode > 47, charcode < 58), AND(charcode > 64, charcode < 91)), character, "") &
                               RIGHT(reference, LEN(reference) - ndx)
              ),
              ndx + 1
          )
      )
  )

 

[lrobbo314]

An option using VBA.

Book1
	Q
1	Vision, mission, values/text and approach
2	VisionMissionValuesTextAndApproach
Sheet1

Cell Formulas
Range	Formula
Q2	Q2	=oa(Q1)

Function OA(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "[a-zA-Z]+"
    .Global = True
    For Each m In .Execute(s)
        OA = OA & StrConv(m.Value, vbProperCase)
    Next m
End With
End Function

 

[Anthony47]

Wonderful Lambda...
This formula combines all the words in column A into one single big string, in case this was the requirement:

=LET(BareUnit,TEXTJOIN("",TRUE,PROPER(A:A)),Splitted,MID(BareUnit,ROW(INDIRECT("1:"&LEN(BareUnit))),1),ChrSpl,CODE(Splitted),TEXTJOIN("",TRUE,FILTER(Splitted,((ChrSpl>64)*(ChrSpl<91))+(ChrSpl>96)*(ChrSpl<123))))

 

[KRice]

I like the other offerings from Anthony and @lrobbo314. Here is a version of Anthony's, modified: 1) to operate only on the cell in column A in the same row, and 2) to include numerals. I'm not sure if the OP wants numbers removed from the strings...if numbers are to be removed, then this gets deleted from the LET function: +(ChrSpl>47)*(ChrSpl<58)

MrExcel_20220505B.xlsx
	A	B
9	123Golf, 5%Hotel: and other things	123Golf5HotelAndOtherThings
Sheet1

Cell Formulas
Range	Formula
B9	B9	=LET(BareUnit,TEXTJOIN("",TRUE,PROPER($A9)),Splitted,MID(BareUnit,ROW(INDIRECT("1:"&LEN(BareUnit))),1),ChrSpl,CODE(Splitted),TEXTJOIN("",TRUE,FILTER(Splitted,(ChrSpl>64)*(ChrSpl<91)+(ChrSpl>96)*(ChrSpl<123)+(ChrSpl>47)*(ChrSpl<58))))

 

[Fluff]

A slightly shorter formula without volatile functions

=LET(BareUnit,CONCAT(PROPER($A9)),Splitted,MID(BareUnit,SEQUENCE(LEN(BareUnit)),1),ChrSpl,CODE(Splitted),CONCAT(FILTER(Splitted,(ChrSpl>64)*(ChrSpl<91)+(ChrSpl>96)*(ChrSpl<123)+(ChrSpl>47)*(ChrSpl<58))))

 

[Peter_SSs]

remove all special characters

If your question has not been answered above already: What is your definition of "special characters"?