Query3. Remove Special Characters from Name/String

Today we have received a Query: How to remove special characters from a Name or String. Will do the same by two ways.

We have a list, which contains special characters.

We will remove these special characters by 2 ways.

1. Replace special characters by macro

Step 1. Open VBE Editor by pressing Alt+F11.

Step 2. Go to Insert > Module

Step 3. Type this code: (Please note: * can not be put alone, it will remove entire the data. Put ~ before * whenever you try to replace *)

Or copy & paste from here

Sub RemoveSpecial()

    

    Const Spcl As String = "!,@,#,$,%,^,&,~*,(,),{,[,],}"   'You can add other characters, as per requirement

    

    For Each char In Split(Spcl, ",")                       'Split all the characters

    Columns("D:D").Replace char, ""                         'Replace special charcters one by one

    Next

End Sub

Step 4. Run this macro by Pressing F5 or you can assign a button as per your requirement

Your output is ready.

2. Creating User Defined Function (UDF)

Step 1. Open VBE Editor by pressing Alt+F11.

Step 2. Go to Insert > Module

Step 3. Type his code:

Or copy & paste from here:

Function GetName(Rng As Range)

    Ln = Len(Rng)
    x = ""                          'Empty variable
    
    For i = 1 To Ln
        Vlue = Mid(Rng, i, 1)
        
        Select Case Asc(Vlue)           'Converts value to character code
            Case 65 To 90, 97 To 122    '65-90 is the Char code of A-Z & 97-122 is the Character code of a-z
            x = x & Vlue                'If Character value matches, move those values into "x" Variable
        End Select
        
        Next i
        
    GetName = x

End Function

Your own function is ready, which is : GetName

Step 4. Type this function into excel sheet, as follows

Your output is ready.

Please note: This function will remove Numeric values as well.

-----------------------------------------------------------------------------------------------------------------------------------------------

Download the file : Remove Special Characters from String.xlsm

Thanks for learning, will come soon with new post.

Please give your valuable feedback/comments.