使用用戶定義函數從文本字符串中刪除一些特殊字符

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), "")
    RemoveSpecial = Str
End Function
3。 然後保存並關閉此代碼，返回到工作表，然後輸入以下公式： = removespecial（A2） 放入要放入結果的空白單元格，請參見屏幕截圖：
4。 然後將填充手柄向下拖動到要應用此公式的單元格上，並且所有不需要的特殊字符都已從文本字符串中刪除，請參見屏幕截圖：
備註：在上面的代碼中，您可以更改特殊字符 ＃$％（）^ *＆ 您想要刪除的其他任何人。
從文本字符串中去除數字，字母或其他特殊字符
Excel的Kutools's 刪除字符 功能可以幫助您快速刪除所有 數字, 阿爾法, 非數字, 非字母, 非印刷, 其他特定字符 從文本字符串根據需要。 點擊下載並立即免費試用Excel的Kutools！   

Excel的Kutools：具有300多個方便的Excel加載項，可以在30天內免費試用，沒有任何限制。 立即下載並免費試用！   
 使用Kutools for Excel從文本字符串中刪除一些特殊字符

如果您不熟悉VBA代碼， Excel的Kutools“ 刪除字符 實用程序可以幫助您快速輕鬆地完成此任務。
安裝後 Excel的Kutools，請如下：
1。 選擇要刪除某些特殊字符的文本字符串。
2. 點擊 庫工具 > 文本 > 刪除字符，請參見屏幕截圖：
3。 在 刪除字符 對話框，檢查 習俗 選項下 刪除字符 部分，然後輸入要刪除的特殊字符，請參見屏幕截圖：
4。 然後點擊 Ok or 申請 按鈕，您在 習俗 文本框已立即從文本字符串中刪除，請參見屏幕截圖：
 演示：使用Kutools for Excel從文本字符串中刪除一些特殊字符

Excel的Kutools：具有300多個方便的Excel加載項，可以在30天內免費試用，沒有任何限制。 立即下載並免費試用！     

最佳辦公生產力工具
使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。  Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...
  閱讀更多... 免費下載...  
Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆
在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！ 
  閱讀更多... 免費下載...  
																	Thanks for this code

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), "")

    RemoveSpecial = Str

End Function

But it is possible that that can remove specific text? like "ab", "abc", "bc" and etc. 															
																	Hi, Nick,

Do you want to remove all ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 characters from the cells?

Looking forward to your reply, thank you!															
xTitleId = "KutoolsforExcel"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

For Each Rng In WorkRng

    xOut = ""

    For i = 1 To Len(Rng.Value)

        xTemp = Mid(Rng.Value, i, 1)

        If xTemp Like "[a-z.]" Or xTemp Like "[A-Z.]" Or xTemp Like "[0-9.]" Then

            xStr = xTemp

            xStr = ""

        End If

        xOut = xOut & xStr

    Next i

    Rng.Value = xOut

End Sub

Hope it can help you!															
																	Hello, Kim, 

The VBA code can only applied in one workbook, if you want to apply it in a new workbook, you should copy and paste the code into your new workbook again.

Thank you!															
																	Hi guys,

I've applied the =removespecial(A2)  code and it works perfectly in one worksheet but then in the other it gives me an invalid #NAME? error.

I checked the "format cells" and it's both on general and I've copied  the same text + formula to both worksheets but it won't work.

Any clue what this might cause this?

Thanks and thank you so much for this code. 

Saves me hours and hours of work!

Regards, Kim															
																	Function GetWordWOSpecChar(Rng As Range)

    'paste in VBA module, Use as a Formula

'Created by Deepak Sharma

    Arr = Array("48", "49", "50", "51", "52", "53", "54", "55", _

            "56", "57", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", _

            "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", _

            "89", "90", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", _

            "107", "108", "109", "110", "111", "112", "113", "114", "115", "116", "117", _

            "118", "119", "120", "121", "122")

    For i = 1 To Len(Rng.Value)

        txt = Mid(Rng.Value, i, 1)

        For g = 1 To UBound(Arr)

            If txt = Chr(Arr(g)) Then GetWord = Right(Rng.Value, Len(Rng.Value) - (i - 1)): Exit Function

        Next g

    Next i

End Function