Excel:從文字字串中刪除字元、單詞、數字
如果你在 Excel 中有一串包含不需要字元、數字或符號的文字字串,可能需要根據其位置或類型刪除特定元素。無論是從左側、右側或中間裁剪字元,還是移除特定字元和數字,Excel 都提供了多種清理資料的方法。本教學將以步驟說明如何利用公式、自訂函數(UDF)及內建功能,輕鬆從文字字串中刪除不需要的字元、單詞和數字。
從文字字串的左側、右側或中間刪除字元
對多數人來說,在 Excel 工作表中從文字字串的左側、右側或中間刪除部分字元是常見的工作。本節將介紹幾個快速簡便的技巧來解決這個問題。
1.1 從文字字串中刪除前 n 個字元
如果你需要從一串文字字串中刪除前 n 個字元,以下方法可以幫助你。
使用公式
一般來說,若要刪除文字字串開頭的字元,可以使用 REPLACE 函數,或結合 RIGHT 與 LEN 函數。
使用 REPLACE 函數刪除前 N 個字元:
- "string":你要從中刪除字元的文字字串;
- "num_chars":你想要刪除的字元數。
例如,若要從儲存格中刪除前2 個字元,請使用以下公式,然後拖曳填滿控點將公式複製到其他儲存格,如下圖所示:
=REPLACE(A4, 1, 2, "")
使用 RIGHT 與 LEN 函數刪除前 N 個字元:
- "string":你要從中刪除字元的文字字串;
- "num_chars":你想要刪除的字元數。
若要從儲存格中刪除前2 個字元,請套用以下公式:
=RIGHT(A4,LEN(A4)-2)
使用自訂函數
你也可以建立自訂函數來從儲存格中刪除前 n 個字元。請依照以下步驟操作:
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:從文字字串中刪除前 n 個字元
Function removeFirstx(rng As String, cnt As Long)
'Updateby Extendoffice
removeFirstx = Right(rng, Len(rng) - cnt)
End Function
3. 回到工作表,在空白儲存格輸入公式「=removefirstx(A4,2)」,然後拖曳填滿控點取得所需結果,如下圖所示:
注意:在此公式中,「A4」為你要刪除字元的儲存格;數字「2」表示你想從文字字串開頭刪除的字元數。
1.2 從文字字串中刪除最後 n 個字元
若要從文字字串右側刪除指定數量的字元,也可以使用公式或自訂函數。
使用公式
要從文字字串中刪除最後 n 個字元,可以結合 LEFT 與 LEN 函數建立公式。
使用 LEFT 與 LEN 函數刪除最後 N 個字元:
- "string":你要從中刪除字元的文字字串;
- "num_chars":你想要刪除的字元數。
若要從文字字串結尾刪除3 個字元,請使用此公式,然後拖曳填滿控點將公式複製到其他儲存格,如下圖所示:
=LEFT(A4, LEN(A4) - 3)
使用自訂函數
自訂函數也能協助你從一串儲存格中刪除最後 n 個字元。請依照以下步驟操作:
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:從文字字串中刪除最後 n 個字元
Function removeLastx(rng As String, cnt As Long)
'Updateby Extendoffice
removeLastx = Left(rng, Len(rng) - cnt)
End Function
3. 回到工作表,在空白儲存格輸入公式「=removelastx(A4,3)」,然後拖曳填滿控點取得所需結果,如下圖所示:
注意:在此公式中,「A4」為你要刪除字元的儲存格;數字「3」表示你想從文字字串結尾刪除的字元數。
1.3 利用強大功能從文字字串中刪除前、後 n 個字元或指定位置的字元
記住各種公式來從左側、右側或指定位置刪除字元可能很麻煩,這時「Kutools for Excel」提供了強大的「刪除某位置字元」功能。利用這個小工具,你無需記憶任何公式,只需幾下點擊即可完成這些操作。
1. 選取你要刪除字元的儲存格,然後點選「Kutools」>「文字」>「刪除某位置字元」,如下圖所示:
2. 在「刪除某位置字元」對話框中,請進行以下操作:
2.1 從儲存格刪除前 n 個字元:
- 在「數字」欄位輸入你要從字串中刪除的字元數。本例中將刪除前2 個字元。
- 在「位置」區塊選擇「從左邊開始」選項。
- 然後點選「確定」或「應用」按鈕,即可得到如下圖所示的結果。
2.2 從儲存格刪除最後 n 個字元:
- 在「數字」欄位輸入你要從字串中刪除的字元數。本例中將刪除最後3 個字元。
- 在「位置」區塊選擇「從右邊開始」選項。
- 然後點選「確定」或「應用」按鈕,即可得到如下圖所示的結果。
2.3 從儲存格指定位置刪除 n 個字元:
如果你需要從字串的某個特定位置開始刪除指定數量的字元,例如從第三個字元開始刪除3 個字元。
- 在「數字」欄位輸入你要刪除的字元數。本例中將從指定位置刪除3 個字元。
- 選擇「自訂」選項,並在「位置」區塊的文字框中輸入你要開始刪除字元的起始位置。本例將從第三個字元開始刪除。
- 然後點選「確定」或「應用」按鈕,即可得到如下圖所示的結果。
Kutools for Excel - 超過 300 種必備工具,讓 Excel 功能更強大。永久免費享受 AI 功能!立即獲取
1.4 使用公式同時從文字字串中刪除前 n 個和最後 n 個字元
當你需要同時從 Excel文字字串的兩側刪除部分字元時,可以結合 MID 與 LEN 函數建立公式來處理這個任務。
- "string":你要從中刪除字元的文字字串;
- "left_chars":要從左側刪除的字元數;
- "right_chars":要從右側刪除的字元數。
例如,你需要同時從文字字串中刪除前7 個字元和最後5 個字元,請在空白儲存格輸入以下公式:
=MID(A4, 7+1, LEN(A4) - (7+5))
注意:在此公式中,「A4」為你要刪除字元的儲存格;數字「7」為你要從左側刪除的字元數;數字「5」為你要從右側刪除的字元數。
然後拖曳填滿控點到你要套用此公式的位置,即可得到如下圖所示的結果:
從文字字串中刪除不需要/特殊字元
當你從其他地方導入資料到 Excel 時,常常會有許多特殊或不需要的字元被貼到工作表中。要刪除這些不需要的字元,例如 #@$%^&、空格、數字、非數字字元、換行符號等,本節將提供一些實用方法協助你處理。
2.1 從文字字串中刪除部分特殊字元
如果文字字串中包含像 %^&*()這類特殊字元,想要刪除這些字元,可以參考以下三種技巧。
使用 SUBSTITUTE 函數從文字字串中刪除多個特殊字元
在 Excel 中,你可以巢狀多個 SUBSTITUTE 函數,將每個特定字元替換為空值,通用語法如下:
- "string_cell":包含你要刪除特殊字元的儲存格;
- "char1, char2, char3":你想要刪除的不需要字元。
現在,請將以下公式複製或輸入到空白儲存格:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "#", ""), "&", ""), "*", ""), "%", "")
然後拖曳填滿控點到你要套用此公式的儲存格,所有你指定的不需要字元將一次被刪除,如下圖所示:
提示:如果你要刪除更多字元,只需在公式中巢狀更多 SUBSTITUTE 函數即可。
使用自訂函數從文字字串中刪除多個特殊字元
上述巢狀 SUBSTITUTE 函數適用於要刪除的特殊字元較少的情況。但如果你有數十個字元要刪除,公式會變得很長且難以維護。這時可以使用下列自訂函數,快速輕鬆完成此任務。
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:從文字字串中刪除多個特殊字元
Function RemoveUnwantedChars(Str As String, xchars As String)
'Updateby Extendoffice
For Index = 1 To Len(xchars)
Str = Replace(Str, Mid(xchars, Index, 1), "")
Next
RemoveUnwantedChars = Str
End Function
3. 關閉程式碼視窗並返回工作表,在空白儲存格輸入公式「=RemoveUnwantedChars(A2, $D$2)」,然後拖曳填滿控點取得所需結果,如下圖所示:
注意:在上述公式中,「A2」為你要刪除字元的儲存格;「$D$2」包含你想要刪除的特殊字元(你可以輸入任何其他需要的特殊字元)。
利用強大功能從文字字串中刪除多個特殊字元
如果你已安裝「Kutools for Excel」,利用其「刪除特定字符」功能,可以根據需求從儲存格清單中刪除各類字元,例如數字字元、字母字元、非打印字符等。
1. 選取你要刪除特殊字元的儲存格區域,然後點選「Kutools」>「文字」>「刪除特定字符」,如下圖所示:
2. 在「刪除特定字符」對話框中:
- 在「刪除特定字符」區塊下勾選「自訂」選項。
- 然後在文字框中輸入你想要刪除的特殊字元。
- 接著點選「確定」或「應用」按鈕,即可一次刪除你指定的字元。如下圖所示:
Kutools for Excel - 超過 300 種必備工具,讓 Excel 功能更強大。永久免費享受 AI 功能!立即獲取
2.2 從文字字串中刪除所有數字
如果你有一串混合數字、字母和特殊字元的文字字串,現在只想刪除所有數字並保留其他字元,本節將提供一些便捷方法協助你。
使用 SUBSTITUTE 函數從文字字串中刪除數字
在 Excel 中,巢狀 SUBSTITUTE 函數可以將所有數字替換為空值,因此你可以使用以下公式從儲存格中刪除所有數字:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")
然後拖曳填滿控點到你要套用此公式的儲存格,所有數字將從文字字串清單中刪除,如下圖所示:
使用 TEXTJOIN 函數從文字字串中刪除數字
如果你使用的是 Excel2019、2021 或365,新的 TEXTJOIN 函數也可以協助你從文字字串中刪除數字。
請將下列公式複製到空白儲存格,然後同時按下 Ctrl + Shift + Enter 鍵取得第一個結果:
=TEXTJOIN("", TRUE, IF(ISERR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))
然後將公式複製到下方其他儲存格,即可套用此公式,如下圖所示:
注意:TEXTJOIN 僅適用於 Excel2019、2021及 Office365。
使用自訂函數從文字字串中刪除數字
除了上述兩種公式外,也可以利用自訂函數協助你,請依照以下步驟操作:
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:從文字字串中刪除數字
Function RemoveNumbers(Txt As String) As String
'Updateby Extendoffice
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(Txt, "")
End With
End Function
3. 關閉並離開程式碼視窗,回到工作表,在空白儲存格輸入「=RemoveNumbers(A2)」,然後拖曳填滿控點到你要套用此公式的儲存格,如下圖所示:
利用便捷選項從文字字串中刪除數字
如果你覺得公式太複雜,現在可以試試「Kutools for Excel」的「刪除特定字符」工具。這個便捷功能只需幾下點擊即可完成此任務。
1. 選取你要刪除數字的儲存格區域,然後點選「Kutools」>「文字」>「刪除特定字符」。
2. 在「刪除特定字符」對話框中,請進行以下操作:
- 在「刪除特定字符」區塊下勾選「數字字符」選項。
- 然後點選「確定」或「應用」按鈕,即可立即刪除所有數字。如下圖所示:
Kutools for Excel - 超過 300 種必備工具,讓 Excel 功能更強大。永久免費享受 AI 功能!立即獲取
2.3 從文字字串中刪除非數字字元
若要從文字字串中刪除所有非數字字元,只保留數字,本節將介紹幾種在 Excel 中解決此任務的方法。
在 Excel2016及更早版本中,使用公式從文字字串中刪除非數字字元
如果你使用的是 Excel2016 或更早版本,需套用較複雜的公式來完成此工作,請將以下公式複製或輸入到空白儲存格:
=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)
然後將公式複製到下方其他儲存格,即可套用此公式,如下圖所示:
注意:如果文字字串中的數字以0 開頭,0會被省略。
在 Excel2019、2021、365 中,使用 TEXTJOIN 函數從文字字串中刪除非數字字元
上述公式對大多數人來說可能較難理解。如果你使用的是 Excel2019、2021 或365,有一個簡潔的公式可以協助你。
請將下列公式複製或輸入到空白儲存格,並同時按下「Ctrl + Shift + Enter」鍵取得第一個正確結果:
=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))
然後拖曳填滿控點到你要套用此公式的儲存格,即可得到如下圖所示的結果:
注意:使用此公式時,前導0會保留,因為數字以文字形式返回。
使用自訂函數從文字字串中刪除非數字字元
當然,你也可以建立自己的自訂函數,語法更簡單,請依照以下步驟操作:
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:從文字字串中刪除非數字字元
Function Removenonnumeric(str As String) As String
'Updateby Extendoffice
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[^0-9]"
Removenonnumeric = .Replace(str, "")
End With
End Function
3. 關閉並離開程式碼視窗,回到工作表,在空白儲存格輸入「=Removenonnumeric(A2)」,然後拖曳填滿控點到你要套用此公式的儲存格,僅數字會被提取出來,如下圖所示:
利用簡單功能從文字字串中刪除非數字字元
若要直接刪除一區儲存格中的非數字字元,「Kutools for Excel」的「刪除特定字符」工具只需幾下點擊即可完成。
1. 選取你要刪除非數字字元的儲存格區域,然後點選「Kutools」>「文字」>「刪除特定字符」。
2. 在「刪除特定字符」對話框中,請設定以下操作:
- 在「刪除特定字符」區塊下勾選「非數字字符」選項。
- 然後點選「確定」或「應用」按鈕,即可立即刪除所有非數字字元。如下圖所示:
Kutools for Excel - 超過 300 種必備工具,讓 Excel 功能更強大。永久免費享受 AI 功能!立即獲取
2.4 將一個儲存格中的文字與數字分離到兩個列
有時你可能想將文字字串中的文字與數字分離到兩個獨立的列,利用下列方法可以快速輕鬆完成此任務。
使用自訂函數將一個儲存格中的文字與數字分離到兩個列
利用下列自訂函數,可以一次提取文字與數字,請依照以下步驟操作:
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:將文字字串中的文字與數字分離到兩個列
Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
SplitText = SplitText + xStr
End If
Next
End Function
3. 關閉並離開程式碼視窗,回到工作表,在空白儲存格輸入「=SplitText(A2,FALSE)」,然後拖曳填滿控點到你要套用此公式的儲存格,即可取得所有文字,如下圖所示:
4. 接著在另一個儲存格輸入「=SplitText(A2,TRUE)」,並拖曳填滿控點到你要套用此公式的儲存格,即可取得所有數字,如下圖所示:
利用簡易功能將一個儲存格中的文字與數字分離到兩個列
如果你有「Kutools for Excel」,其「分割儲存格」工具可以根據分隔符、指定寬度或文字與數字,將儲存格分割為多個列或行。
1. 選取你要分割的儲存格區域,然後點選「Kutools」>「合併與分割」>「分割儲存格」,如下圖所示:
2. 在「分割儲存格」對話框中,於「類型」區塊選擇「資料分欄」選項,然後在「分隔依據」區塊勾選「拆分為文本和數字」,如下圖所示:
3. 然後點選「確定」按鈕,會再跳出一個「分割儲存格」對話框,選擇一個儲存格作為分離後的文字與數字輸出位置,然後點選「確定」。現在你可以看到選取的儲存格中的文字與數字已同時分割到兩個列,如下示範所示:
Kutools for Excel - 超過 300 種必備工具,讓 Excel 功能更強大。永久免費享受 AI 功能!立即獲取
2.5 從文字字串中刪除換行字元
換行符號讓你能在 Excel 的同一儲存格內顯示多行內容。有時從網站複製資料或手動用「Alt + Enter」分行時,會產生換行或回車符號。某些情況下,你可能想要刪除這些換行符號,讓儲存格內容顯示為單行,如下圖所示。本節將介紹幾種在 Excel 中解決此任務的方法。
使用尋找和替換功能從文字字串中刪除換行字元
在 Excel 中,你可以利用「尋找和替換」功能刪除換行符號,請依照以下步驟操作:
1. 選取你要刪除換行符號的資料區域。
2. 點選「常用」>「尋找與選取」>「取代」(或按下「Ctrl + H」鍵)開啟「尋找和取代」對話框,如下圖所示:
3. 在彈出的「尋找和取代」對話框中,請進行以下操作:
- 將游標放在「尋找內容」欄位,然後在鍵盤上按下「Ctrl + J」,你可能看不到任何內容,但換行符號已插入。
- 在「替換為」欄位,若要刪除換行符號請留空,或按一次「空格鍵」將換行符號替換為空格。
4. 然後點選「全部替換」按鈕,選取儲存格中的所有換行符號將一次被刪除或替換為空格。如下圖所示:
使用 SUBSTITUTE 函數從文字字串中刪除換行字元
你也可以結合 SUBSTITUTE 與 CHAR 函數建立公式,從文字字串中刪除換行符號。
請套用以下公式取得結果:
=SUBSTITUTE(A2,CHAR(10),"")
提示:SUBSTITUTE 函數會尋找並替換代表換行符號的 CHAR(10) 字元為空值。如果你想用逗號加空格分隔結果,可以使用下列公式:
=SUBSTITUTE(A2,CHAR(10),", ")
使用 VBA 程式碼從文字字串中刪除換行字元
如果你熟悉 VBA 程式碼,這裡也提供一段程式碼,請依照以下步驟操作:
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:從文字字串中刪除換行符號
Sub RemoveCarriage()
'Updateby Extendoffice
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
Rng.Value = Replace(Rng.Value, Chr(10), "")
Next
End Sub
3. 按下「F5」鍵執行程式碼,會跳出提示框。選取你要刪除換行符號的區域,如下圖所示:
4. 然後點選「確定」按鈕,所有換行符號將從選取的資料區域中刪除。
利用智慧選項從文字字串中刪除換行字元
這裡,「Kutools for Excel」的「刪除特定字符」功能也能輕鬆協助你刪除換行符號。
1. 選取你要刪除換行符號的儲存格區域,然後點選「Kutools」>「文字」>「刪除特定字符」。
2. 在「刪除特定字符」對話框中,請設定以下操作:
- 在「刪除特定字符」區塊下勾選「非打印字符」選項。
- 然後點選「確定」或「應用」按鈕,即可從選取的資料區域中刪除所有換行符號。如下圖所示:
Kutools for Excel - 超過 300 種必備工具,讓 Excel 功能更強大。永久免費享受 AI 功能!立即獲取
2.6 從文字字串中刪除空格(前置、後置、多餘或所有空格)
從外部來源複製貼上文字到 Excel 工作表時,常會帶入一些惱人的多餘空格,手動刪除前置、後置或多餘空格會很繁瑣。幸好,Excel 提供了一些簡單技巧來處理這個問題。
使用 TRIM 函數從文字字串中刪除多餘空格(前置、後置、多餘)
在 Excel 中,若要刪除文字字串的前置、後置及多餘空格,可以使用簡單的 TRIM 函數。此函數會刪除所有空格,只保留單詞間的單一空格。
請在空白儲存格輸入以下公式:
=TRIM(A2)
然後拖曳填滿控點將公式複製到其他儲存格,現在你可以看到所有前置、後置及單詞間多餘空格已一次移除,如下圖所示:
從文字字串中刪除所有空格
如果你想從文字字串中刪除所有空格,可以使用以下 SUBSTITUTE 函數或尋找與取代功能。
使用 SUBSTITUTE 函數
你可以利用 SUBSTITUTE 函數將所有空格替換為空值,請在空白儲存格輸入以下公式:
=SUBSTITUTE(A2," ","")
然後拖曳填滿控點將公式複製到其他需要的儲存格,所有空格將被刪除,如下圖所示:
使用尋找與取代功能
事實上,Excel 的「尋找與取代」功能也能協助你從選取儲存格中刪除所有空格,請依照以下步驟操作:
1. 選取你要刪除所有空格的資料區域。
2. 點選「常用」>「尋找與選取」>「取代」(或按下「Ctrl + H」鍵)開啟「尋找與取代」對話框,在開啟的對話框中,請進行以下操作:
- 在「尋找內容」欄位按下「空格鍵」;
- 在「替換為」欄位請留空。
3. 然後點選「全部替換」按鈕,選取儲存格中的所有空格將一次被刪除。如下圖所示:
利用強大功能從文字字串中刪除各類空格
「Kutools for Excel」有一項強大的「刪除空格」功能,利用此工具,你不僅可以刪除前置空格、後置空格、多餘空格,還能一次刪除所有空格,提升工作效率。
1. 選取你要刪除空格的資料區域,然後點選「Kutools」>「文字」>「刪除空格」。如下圖所示:
2. 在「刪除空格」對話框中,從「空格類型」選擇你要刪除的空格類型:
- 若要刪除前置空格,請選擇「前面的空格」選項;
- 若要刪除後置空格,請選擇「後面的空格」選項;
- 若要同時刪除前置與後置空格,請選擇「前面的空格 & 後面的空格」選項;
- 若要刪除所有多餘空格,請選擇「所有多餘的空格」選項;
- 若要刪除所有空格,請選擇「所有的空格」選項。
3. 然後點選「確定」或「應用」按鈕,即可得到你需要的結果。
Kutools for Excel - 超過 300 種必備工具,讓 Excel 功能更強大。永久免費享受 AI 功能!立即獲取
刪除特定字元前或後的字元/文字
本節將介紹如何刪除第一個、最後一個或第 N 次出現的特定字元前或後的文字或字元。
3.1 刪除第一個特定字元前或後的文字
如果你想從一串文字字串中刪除第一個特定字元(如空格、逗號)前或後的文字,如下圖所示,這裡提供兩種方法。
使用公式刪除第一個特定字元前的文字
要刪除第一個特定字元前的文字或字元,可以結合 RIGHT、LEN 與 FIND 函數建立公式,通用語法如下:
- "cell":你要從中刪除文字的儲存格參照或文字字串;
- "char":你要根據其刪除文字的特定分隔符。
例如,若要從字串清單中刪除第一個逗號前的所有內容,請在空白儲存格輸入以下公式,然後拖曳到其他儲存格,如下圖所示:
=RIGHT(A2,LEN(A2)-FIND(",",A2))
注意:在上述公式中,「A2」為你要刪除文字的儲存格;「,」為你要根據其刪除文字的特定字元,你可根據需要更換為其他字元。
使用公式刪除第一個特定字元後的文字
要刪除第一個特定字元後的所有內容,可以利用 LEFT 與 FIND 函數取得結果,通用語法如下:
- "cell":你要從中刪除文字的儲存格參照或文字字串;
- "char":你要根據其刪除文字的特定分隔符。
請在空白儲存格輸入以下公式,然後拖曳填滿控點到其他儲存格,所有第一個逗號後的字元將一次被刪除,如下圖所示:
=LEFT(A2,FIND(",",A2)-1)
3.2 刪除第 N 次出現的字元前或後的文字
有時文字字串中包含多個特定分隔符,你可能想根據第二、第三或第四次出現的分隔符刪除其前或後的所有內容。針對這類需求,可以參考以下技巧:
使用公式刪除第 N 次出現的字元前的文字
要刪除第 N 次出現的特定字元前的文字,以下公式可以協助你,通用語法如下:
- "cell":你要從中刪除文字的儲存格參照或文字字串;
- "char":你要根據其刪除文字的特定分隔符;
- "N":你要刪除其前文字的第 N 次出現的字元。
例如,若要從文字字串中刪除第二個逗號前的所有內容,請套用以下公式:
=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2,",","#",2)))
注意:在上述公式中,「A2」為你要刪除文字的儲存格;「,」為你要根據其刪除文字的特定字元,你可根據需要更換為其他字元;「2」表示你要刪除其前文字的第 N 次逗號。
然後拖曳填滿控點將公式複製到其他儲存格,如下圖所示:
使用公式刪除第 N 次出現的字元後的文字
要刪除第 N 次出現的特定分隔符後的所有內容,可以結合 LEFT、SUBSTITUTE 與 FIND 函數,通用語法如下:
- "cell":你要從中刪除文字的儲存格參照或文字字串;
- "char":你要根據其刪除文字的特定分隔符;
- "N":你要刪除其後文字的第 N 次出現的字元。
了解基本語法後,請將以下公式複製或輸入到空白儲存格:
=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)
注意:在上述公式中,「A2」為你要刪除文字的儲存格;「,」為你要根據其刪除文字的特定字元,你可根據需要更換為其他字元;「2」表示你要刪除其後文字的第 N 次逗號。
然後拖曳填滿控點將公式複製到其他儲存格,所有第二個逗號後的字元將一次被刪除,如下圖所示:
使用自訂函數刪除第 N 次出現的字元前或後的文字
如你所見,利用 Excel 原生函數的不同組合可以解決刪除第 N 次出現的字元前或後文字的情境。問題是你需要記住這些複雜公式。這時可以建立自訂函數涵蓋所有情境,請依照以下步驟操作:
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:刪除第 N 次出現的字元前或後的文字
Function RemoveTextOccurrence(Str As String, Delimiter As String, Occurrence As Integer, IsAfter As Boolean)
Dim xStr As String
Dim xStrLen, xF, xIntStart As Integer
xStr = Str
xStrLen = Len(xStr)
xIntStart = 1
For xF = 1 To Occurrence
xIntStart = InStr(xIntStart + 1, xStr, Delimiter, vbTextCompare)
If (xIntStart = 0) Or (xIntStart < 0) Then
If IsAfter Then
RemoveTextOccurrence = xStr
Else
RemoveTextOccurrence = ""
End If
Exit Function
End If
Next
If IsAfter Then
RemoveTextOccurrence = Mid(Str, 1, xIntStart - 1)
Else
RemoveTextOccurrence = Mid(Str, xIntStart + 1)
End If
End Function
3. 關閉並離開程式碼視窗,回到工作表,使用以下公式:
刪除第二個逗號前的文字:
=RemoveTextOccurrence(A2, ", ", 2, FALSE)
刪除第二個逗號後的文字
=RemoveTextOccurrence(A2, ", ", 2, TRUE)
3.3 刪除最後一次出現的字元前或後的文字
如果你需要刪除最後一個特定字元前或後的所有文字,只保留其前或後的子字串,本節將提供公式協助你解決此問題。
使用公式刪除最後一次出現的字元前的文字
要刪除最後一次出現的字元前的所有內容,通用語法如下:
- "cell":你要從中刪除文字的儲存格參照或文字字串;
- "char":你要根據其刪除文字的特定分隔符;
若要刪除最後一個逗號前的所有內容,請將以下公式複製或輸入到空白儲存格:
=RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,",","#",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))
注意:在上述公式中,「A2」為你要刪除文字的儲存格;「,」為你要根據其刪除文字的特定字元,你可根據需要更換為其他字元。
然後拖曳填滿控點將公式複製到其他儲存格,所有最後一個逗號前的字元將被刪除,如下圖所示:
使用公式刪除最後一次出現的字元後的文字
如果儲存格值由不定數量的分隔符分隔,且你想刪除最後一個分隔符後的所有內容,通用語法如下:
- "cell":你要從中刪除文字的儲存格參照或文字字串;
- "char":你要根據其刪除文字的特定分隔符;
請將以下公式複製或輸入到空白儲存格,然後拖曳填滿控點取得其他結果,如下圖所示:
=LEFT(A2,FIND("#",SUBSTITUTE(A2,",>","#",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)
「注意」:在上述公式中,「A2」為你要刪除文字的儲存格;「,」為你要根據其刪除文字的特定字元,你可根據需要更換為其他字元。
3.4 刪除括號內的文字
如果你有一串文字字串,其中部分字元被括號括住,現在你可能想要刪除括號內的所有字元(包含括號本身),如下圖所示。本節將介紹幾種在 Excel 中解決此任務的技巧。
使用尋找和替換功能刪除括號內的文字
在 Excel 中,內建的尋找與取代功能可以協助你尋找所有括號內的文字,然後將其替換為空值。請依照以下步驟操作:
1. 選取你要刪除括號內文字的資料清單。
2. 點選「常用」>「尋找與選取」>「取代」(或按下「Ctrl + H」鍵)開啟尋找與取代對話框,在對話框中進行以下操作:
- 在「尋找內容」欄位輸入「(*)」;
- 在「替換為」欄位請留空。
3. 然後點選「全部替換」按鈕,選取儲存格中所有括號內(包含括號)的字元將一次被刪除。如下圖所示:
提示:尋找與取代功能同樣適用於文字字串中有兩對或以上括號的情況。
使用公式刪除括號內的文字
除了尋找與取代功能外,也可以利用公式解決此任務,通用語法如下:
- "text":你要從中刪除字元的文字字串或儲存格參照。
請將下列公式複製或輸入到你要取得結果的空白儲存格:
=SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),"")
然後拖曳填滿控點到你要套用此公式的儲存格,所有括號內(包含括號)的文字將一次被刪除,如下圖所示:
提示:如果儲存格值中沒有括號,套用上述公式會顯示錯誤,若要忽略錯誤,請使用下列公式:
=IFERROR(SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),""),A2)
使用自訂函數刪除括號內的文字
上述公式適用於刪除單一對括號內的文字。如果你需要刪除多對括號內的內容,公式將無法正確運作。這時可以建立簡單的自訂函數來解決此任務。
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:刪除括號內的文字
Function remtxt(ByVal str As String) As String
'Updateby Extendoffice
While InStr(str, "(") > 0 And InStr(str, ")") > InStr(str, "(")
str = Left(str, InStr(str, "(") - 1) & Mid(str, InStr(str, ")") + 1)
Wend
remtxt = Trim(str)
End Function
3. 回到工作表,在空白儲存格輸入公式「=remtxt(A2)」,然後拖曳填滿控點套用公式。所有括號內(包含括號)的文字將被刪除,如下圖所示:
從文字字串中刪除單詞
某些情況下,你可能想從一串儲存格中刪除部分單詞,例如第一個或最後一個單詞、重複單詞等。針對這類刪除需求,本節將介紹幾種方法。
4.1 從文字字串中刪除第一個或最後一個單詞
若要從一串文字字串中刪除第一個或最後一個單詞,以下公式可以協助你。
使用公式刪除文字字串中的第一個單詞
要刪除一串文字字串中的第一個單詞,可以結合 RIGHT、LEN 與 FIND 函數建立簡單公式,通用語法如下:
- "text":你要從中刪除第一個單詞的文字字串或儲存格參照。
請將下列公式輸入或複製到空白儲存格:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
然後拖曳填滿控點套用公式到其他儲存格,如下圖所示:
提示:若要從儲存格中刪除前 N 個單詞,請使用下列公式:
- "text":你要從中刪除前 n 個單詞的文字字串或儲存格參照;
- "N":表示你想從文字字串開頭刪除的單詞數。
例如,若要從儲存格中刪除前兩個單詞,請將下列公式複製或輸入到空白儲存格取得所需結果,如下圖所示:
=MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",2)),255)
使用公式刪除文字字串中的最後一個單詞
要刪除文字字串中的最後一個單詞,也可以利用公式解決,通用語法如下:
- "text":你要從中刪除最後一個單詞的文字字串或儲存格參照;
請將下列公式輸入到空白儲存格,然後拖曳填滿控點套用公式到其他儲存格,如下圖所示:
=LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)
提示:若要從一串儲存格中刪除最後 N 個單詞,通用語法如下:
- "text":你要從中刪除最後 n 個單詞的文字字串或儲存格參照;
- "N":表示你想從文字字串結尾刪除的單詞數。
假設要從一串儲存格中刪除最後3 個單詞,請使用下列公式取得結果,如下圖所示:
=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-(3-1))))
4.2 刪除儲存格內重複的字元或單詞
在刪除重複值或行時,Excel 提供了多種選項,但若要刪除儲存格內的重複字元或單詞,內建功能可能無法直接解決。這時可透過自訂函數來處理此問題。
使用自訂函數刪除儲存格內重複的字元
如果一個儲存格中有多個相同字元,想要刪除重複字元並僅保留第一次出現的字元,如下圖所示,可以使用下列自訂函數。
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:刪除儲存格內重複字元
Function RemoveDupeschars(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
xChar = VBA.Mid(xValue, i, 1)
If xDic.Exists(xChar) Then
Else
xDic(xChar) = ""
xOutValue = xOutValue & xChar
End If
Next
RemoveDupeschars = xOutValue
End Function
3. 關閉程式碼視窗,回到工作表,在資料旁的空白儲存格輸入公式「=RemoveDupeschars(A2)」,然後拖曳填滿控點到你要套用此公式的儲存格,如下圖所示:
注意:「A2」為你要刪除重複字元的資料儲存格。
提示:此函數區分大小寫,會將大寫與小寫視為不同字元。
使用自訂函數刪除儲存格內重複的單詞
假設你有一個儲存格中有多個相同單詞,想要刪除所有重複單詞,如下圖所示。你可以使用下列自訂函數在 Excel 中解決此任務。
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:刪除儲存格內重複單詞
Function RemoveDupeswords(txt As String, Optional delim As String = " ") As String
'Updateby Extendoffice
Dim x
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each x In Split(txt, delim)
If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
Next
If .Count > 0 Then RemoveDupeswords = Join(.keys, delim)
End With
End Function
3. 關閉程式碼視窗,回到工作表,在資料旁的空白儲存格輸入公式「=RemoveDupeswords(A2,", ")」,然後拖曳填滿控點到你要套用此公式的儲存格,如下圖所示:
注意:「A2」為你要刪除重複單詞的儲存格,逗號與空格(",")為分隔字串的分隔符,你可根據需求更換為其他分隔符。
提示:此函數不區分大小寫,會將大寫與小寫視為相同字元。
4.3 將文字字串裁剪為 N 個單詞
如果一個儲存格中有很長的文字字串,有時你可能只想保留前 n 個單詞並裁剪掉其餘單詞。本節將介紹幾種在 Excel 中協助你完成此工作的技巧。
使用公式將文字字串裁剪為 N 個單詞
要將文字字串裁剪為 N 個單詞,可以結合 LEFT、FIND 與 SUBSTITUTE 函數建立公式,通用語法如下:
- "text":你要裁剪的文字字串或儲存格參照;
- "N":你想從左側保留的單詞數。
請將下列公式複製或輸入到空白儲存格:
=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",B2))-1)
然後拖曳填滿控點套用公式到其他儲存格,如下圖所示:
使用自訂函數將文字字串裁剪為 N 個單詞
除了上述公式外,你也可以建立自訂函數來解決此任務,請依照以下步驟操作:
1. 按下「Alt + F11」鍵開啟「Microsoft Visual Basic for Applications」視窗。
2. 點選「插入」>「模組」,並將下列程式碼貼到模組視窗中。
VBA 程式碼:將文字字串裁剪為 N 個單詞
Function GetNWords(StrWords As String, Num_of_Words As Integer) As String
'Updateby Extendoffice
Dim xArr
Dim xRes As String
Dim xF As Integer
xStr = StrWords
If (Num_of_Words < 1) Then
GetNWords = ""
Exit Function
End If
xArr = Split(xStr, " ")
xRes = ""
On Error Resume Next
For xF = 0 To UBound(xArr)
If Trim(xArr(xF)) <> "" Then
Num_of_Words = Num_of_Words - 1
If xRes = "" Then
xRes = Trim(xArr(xF))
Else
xRes = xRes & " " & Trim(xArr(xF))
End If
End If
If Num_of_Words = 0 Then Exit For
Next
If Num_of_Words = 0 Then
GetNWords = xRes & "..."
Else
GetNWords = xRes & "..."
End If
End Function
3. 關閉程式碼視窗,回到工作表,在空白儲存格輸入公式「=GetNWords(A2,B2)」,然後拖曳填滿控點套用公式到其他儲存格,只保留前指定數量的單詞,如下圖所示:
最佳辦公效率工具
🤖 | Kutools AI 助手:基於智能執行方式革新數據分析:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記重複值或識別重複項 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入無需公式 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
列管理器: 添加特定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 比較區域和列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫(自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/斜體/刪除線...) ... | |
頂級 15 種工具集: 12 個文本工具(添加文本、刪除特定字符、...) | 50+ 圖表 類型(甘特圖、...) | 40+ 實用 公式(基於生日計算年齡、...) | 19 個插入工具(插入QR碼、根據路徑插入圖片、...) | 12 個轉換工具(金額轉大寫、匯率轉換、...) | 7 個合併與分割工具(高級合併行、分割儲存格、...) | ... 還有更多 |
使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。 點擊這裡獲取您最需要的功能...
Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
- 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
- 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!