Removing Non-Alphanumeric Characters (without using macros or script!)

[ellison]

Arrghhhhhhhhh I've been trying to do this for weeks!

We have a report which arrives with 30,000 lines of different part numbers.

And we need to extract just the alpha(A-Z or a-z) numeric (0-9) characters from it, leaving out all of the dahses, slashes, underscores, spaces and everything else that people manage to include.
For example

AS-B___8/4%9-016d needs to be ASB849016D

Ab'D-A/9(00)-7[5-M-T8"16 needs to be ABDA90075MT816

The only solutions I can find involve macros, script & UDF's....and that's just about smashed my learning to pieces!!!

Would anybody have a version which could help us to get this done using those?

PS Wonderful site gents, many many thanks!

 

[pravishini]

If I dont want spaces between the names to be deleted, what should I do. Spaces before the name and after the name can be removed, but not in between the names please

Very difficult and messy to do this without VBa.

Try this UDF

To use this you need to do this.

1/. In Excel, with your workbook open, press Alt+f11 this will take you to the VBa Editor.

2/. With the "Insert" Tab choose "Module"

3/. In the resulting window paste all of 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

Use in Excel like so ...

=CleanCode(A1)

 

[Michael M]

You do realise you have jumped into a thread that is 7 years old !!
Have you looked at the TRIM function

=TRIM(A1)

Will remove leading and trailling spaces