Formula to eliminate all but alpha characters

Question

I need to scrub a column of names in Excel to eliminate all non-Alpha characters including periods, commas, spaces, hyphens and apostrophes.

EXAMPLE: Change O'Malley-Smith, Tom, Jr. to OMALLEYSMITHTOMJR

The client requires this to be an Excel function, otherwise I'd make it easy with a quick Java program similar to replaceAll("[^a-zA-Z]", "").toUpperCase(). I cannot seem to find anything that looks like an off-the-shelf function to do this outside of a whole mess of SUBSTITUTE functions - which only seem to be available one-per-cell.

I'm not terribly fluent with developing custom macros if that's what I need.

Answer 1

I had a similar need sometime ago and found something that worked great.

Press Alt+F11 to open the Visual Basic editor. Insert a new Module and paste the following code.

Function CleanCode(Rng As Range)
    Dim strTemp As String
    Dim n As Long

    For n = 1 To Len(Rng)
        Select Case Asc(Mid(UCase(Rng), n, 1))
            Case 48 To 57, 65 To 90
                strTemp = strTemp & Mid(UCase(Rng), n, 1)
        End Select
    Next
    CleanCode = strTemp
End Function

CleanCode now is new function and you can use it as a formula.

So next to the cell with the string you want to manipulate just copy =CleanCode(yourcell)

Answer 2

Indeed a mess of SUBSTITUTEs but within a single cell is possible, eg:

=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),"'",""),".",""),"-",""))   

Of course may need to be 'extended' to cover other non-alpha characters.