Excel - Remove Some Special Characters From Text String With User Defined Function

Remove Some Special Characters From Text String With User Defined Function

The following VBA code can help you to remove the specific characters you need, please do as follows:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Remove some special characters from text string
1
2
3
4
5
6
7
8
9
10
Function RemoveSpecial(Str As StringAs String
'updatebyExtendoffice 20160303
    Dim xChars As String
    Dim As Long
    xChars = "#$%()^*&"
    For I = 1 To Len(xChars)
        Str = Replace$(Str, Mid$(xChars, I, 1), "")
    Next
    RemoveSpecial = Str
End Function
3. Then save and close this code, go back to the worksheet, and enter this formula: =removespecial(A2) into a blank cell where you want to put the result, see screenshot:
doc remove special characters 1
4. And then drag the fill handle down to the cells which you want to apply this formula, and all the special characters that you needn’t have been removed from the text string, see screenshot:
doc remove special characters 2
Note: In the above code, you can change the special characters #$%()^*& to any others that you want to remove.
Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.
    Blogger Comment

0 comments:

Post a Comment

Note: Only a member of this blog may post a comment.