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

Remove Some Special Characters From Text String With User Defined Function

Source: https://www.extendoffice.com/documents/excel/3483-excel-remove-special-characters.html

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 String) As String 'updatebyExtendoffice 20160303     Dim xChars As String     Dim I 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:

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:

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.