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!

 

[cyrilbrd]

Hi, Vba would be the fastest way considering your thread (30000 rows)

Consider:

Function MSUBSTITUTE(ByVal trStr As Variant, frStr As String, toStr As String) As Variant

' Created by Juan Pablo González

' with ideas from Aladin Akyurek

'toStr is assumed to be the same length of frStr.  If not, the remaining characters

'will be considered as null ("").



'Note that this function IS case sensitive.  To replace all instances of "a" you need

'to use "a" AND "A"



'You can't replace one character with two characters. This

'=MSUBSTITUTE("This is a test","i","$@")

'will result in this:

'"Th$s $s a test"



    Dim iRow As Integer

    Dim iCol As Integer

    Dim j As Integer

    Dim Ar As Variant

    Dim vfr() As String

    Dim vto() As String

    

    ReDim vfr(1 To Len(frStr))

    ReDim vto(1 To Len(frStr))

    

    For j = 1 To Len(frStr)

        vfr(j) = Mid(frStr, j, 1)

        If Mid(toStr, j, 1) <> "" Then

            vto(j) = Mid(toStr, j, 1)

        Else

            vto(j) = ""

        End If

    Next j

    

    If IsArray(trStr) Then

        Ar = trStr

        For iRow = LBound(Ar, 1) To UBound(Ar, 1)

            For iCol = LBound(Ar, 2) To UBound(Ar, 2)

                For j = 1 To Len(frStr)

                    Ar(iRow, iCol) = Application.Substitute(Ar(iRow, iCol), vfr(j), vto(j))

                Next j

            Next iCol

        Next iRow

    Else

        Ar = trStr

        For j = 1 To Len(frStr)

            Ar = Application.Substitute(Ar, vfr(j), vto(j))

        Next j

    End If

    MSUBSTITUTE = Ar

End Function

formula would be such as =TRIM(SUBSTITUTE(Msubstitute(A1,"(){}[]|\-'_!@#$%:;^&/.<>*#.","")," "" "," "))
No limit to the values to be excluded.

 

[ellison]

hi many thanks for the answer, but we just can't follow the VBA I'm afraid!
We did try your formula:

=TRIM(SUBSTITUTE(Msubstitute(A1,"(){}[]|\-'_!@#$%:;^&/.<>*#.","")," "" "," "))

But the answer it keeps giving is #NAME?

Do we need to define the list? Or give the range a name? Or are we just entering it wrong??!!

Apologies - we're at the outer-limits of excel knowledge here!

 

[Marcol]

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)

 

[ellison]

oh my gosh, that's WONDERFUL!!!!!!!

We have now completed our first ever UDF, no stopping us now!!!!!!

Many, many thanks!

 

[Marcol]

We are always happy to have helped. ...

Don't get carried away with VBa, learn Excel functions first!

VBa has its' place, but it's, more often than not, slower than native formulae in larger sheets.

Some situations are better with VBa, and there are things that can only be done with VBa, but it shouldn't be seen as an easy/lazy way out.

 

[MikeWx]

If you have the MOREFUNC add in installed with your Excel, the following array formula will also do what you want for data in Cell A1. Press CNTL SHIFT ENTER instead of just ENTER.

=MCONCAT(IF(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>47,IF(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<58,MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1),IF(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))>64,IF(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))<91,MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1),""),"")),""))

Mike

 

[SiobhanhAllen]

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)

This is fantastic. Thank you - It's so clear and I never knew you could make a UDF - thank you so much

 

[Nosmada]

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)

Thanks Marcol! Unfortunately I can't seem to get the result to show in the cell. The cell only shows the formula. The only way I can see the result is by clicking on the insert function button? Anyone have any ideas how to get this working fully? Thanks

 

[lhiggs]

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

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!

I know you already found your solution years ago, but for anybody else who is hell-bent on not using any VBA in their file, you can easily write/copy a formula that substitutes all 32 special characters #$%&'()*+,-./:;< with nothing. Its 33 if you don't want spaces either. Start with the original substitution string like this "=SUBSTITUTE(A2,CHAR(32),"")" Then you just reference each new string created within the same formula for each special character's assigned code. It would look something like this...

=UPPER(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(32),""),CHAR(33),""),CHAR(34),""),CHAR(35),""),CHAR(36),""),CHAR(37),""),CHAR(38),""),CHAR(39),""),CHAR(40),""),CHAR(41),""),CHAR(42),""),CHAR(43),""),CHAR(44),""),CHAR(45),""),CHAR(46),""),CHAR(47),""),CHAR(58),""),CHAR(59),""),CHAR(60),""),CHAR(61),""),CHAR(62),""),CHAR(63),""),CHAR(64),""),CHAR(91),""),CHAR(92),""),CHAR(93),""),CHAR(94),""),CHAR(95),""),CHAR(96),""),CHAR(123),""),CHAR(124),""),CHAR(125),""),CHAR(126),"")))


It's not elegant and "pretty", but I imagine it gets the job done for most people's needs