跳到主要內容

如何從Excel中的字符串中刪除一些特殊字符？

如果有一些特殊字符，例如 ％^＆*（） 現在，您要從單元格字符串中刪除這些特定字符。 要一一刪除它們很費時，在這裡，我將介紹一些快速技巧來解決Excel中的這一任務。

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

使用Kutools for Excel從文本字符串中刪除一些特殊字符

箭頭藍色右氣泡 使用用戶定義函數從文本字符串中刪除一些特殊字符

以下VBA代碼可以幫助您刪除所需的特定字符，請執行以下操作：

1. 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊，然後將以下代碼粘貼到 模塊 窗口。

VBA代碼：從文本字符串中刪除一些特殊字符

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。 然後保存並關閉此代碼，返回到工作表，然後輸入以下公式： = removespecial（A2） 放入要放入結果的空白單元格，請參見屏幕截圖：

doc刪除特殊字符1

4。 然後將填充手柄向下拖動到要應用此公式的單元格上，並且所有不需要的特殊字符都已從文本字符串中刪除，請參見屏幕截圖：

doc刪除特殊字符2

備註：在上面的代碼中，您可以更改特殊字符 ＃$％（）^ *＆ 您想要刪除的其他任何人。

從文本字符串中去除數字，字母或其他特殊字符

Excel的Kutools's 刪除字符 功能可以幫助您快速刪除所有 數字, 阿爾法, 非數字, 非字母, 非印刷, 其他特定字符 從文本字符串根據需要。 點擊下載並立即免費試用Excel的Kutools！

doc刪除特殊字符7

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

如果您不熟悉VBA代碼， Excel的Kutools“ 刪除字符 實用程序可以幫助您快速輕鬆地完成此任務。

安裝後 Excel的Kutools，請如下：

1。 選擇要刪除某些特殊字符的文本字符串。

2. 點擊 庫工具 > 文本 > 刪除字符，請參見屏幕截圖：

3。 在 刪除字符 對話框，檢查 習俗 選項下 刪除字符 部分，然後輸入要刪除的特殊字符，請參見屏幕截圖：

doc刪除特殊字符4

4。 然後點擊 Ok or 登記 按鈕，您在 習俗 文本框已立即從文本字符串中刪除，請參見屏幕截圖：

doc刪除特殊字符5 2 doc刪除特殊字符6

點擊下載並立即免費試用Excel的Kutools！

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

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (11)
No ratings yet. Be the first to rate!
Linny Anthony
Linny Anthony
This comment was minimized by the moderator on the site
Excelllent .. Five Stars.. thanks a lot
Reply
0
0
Cris
Cris
This comment was minimized by the moderator on the site
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), "")
Next
RemoveSpecial = Str
End Function

But it is possible that that can remove specific text? like "ab", "abc", "bc" and etc.
Reply
0
0
minhys1
minhys1
This comment was minimized by the moderator on the site
With Emoji need remove, how ?
Reply
0
0
gndecastro@gmail.com
This comment was minimized by the moderator on the site
Rather than searching for specific special characters, how about if you want to search for and replace ALL special characters. In other words, how would you write the search for NOT one of the following characters: "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
Reply
Report
0
0
skyyang
skyyang    
This comment was minimized by the moderator on the site
Hi, Nick,
Do you want to remove all ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 characters from the cells?
Looking forward to your reply, thank you!
Reply
Report
0
0
gndecastro@gmail.com
    skyyang
This comment was minimized by the moderator on the site
No. I want to keep only "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
Reply
Report
0
0
skyyang
skyyang    
This comment was minimized by the moderator on the site
Nick,
Maybe the below VBA code can solve your problem, please try:

Sub RemoveNotAlphasNotNum()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
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
Else
xStr = ""
End If
xOut = xOut & xStr
Next i
Rng.Value = xOut
Next
End Sub


Hope it can help you!
Reply
Report
0
0
Kim
Kim
This comment was minimized by the moderator on the site
Hmm I wonder, does it only work in the worksheet you've originally pasted the code in?
As for that one it only seems to work, not for any new workbook you open
Reply
0
0
skyyang
skyyang     Kim
This comment was minimized by the moderator on the site
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!
Reply
Report
0
0
Kim
Kim
This comment was minimized by the moderator on the site
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
Reply
0
0
Deepak
Deepak
This comment was minimized by the moderator on the site
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
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations