Note: The other languages of the website are Google-translated. Back to English

Excel 教程 - 按特定位置提取文本或數字

很多情況下,你可能只需要從一個句子或單元格中的文本字符串中提取有用的內容,例如從地址中提取省份,從句子中提取電子郵件地址,從對話中提取快遞單號,等等。 本教程將提取範圍縮小到單元格中的特定位置,並收集了不同的方法來幫助在 Excel 中按特定位置從單元格中提取文本或數字。

本教程主要介紹: (單擊下面或右邊的目錄中的任何標題,以導航到相應的內容。)


按位置提取文本

本節收集可以從單元格中提取文本的常見位置,並提供相應的方法來逐步處理它們。 您可以瀏覽以了解更多詳細信息。

1.從左或右提取字符數

要從字符串的左側或右側提取字符數,您可以嘗試以下方法之一。

1.1 用公式提取前N個字符或後N個字符

假設您在 B 列中有一個文本字符串列表,如下面的屏幕截圖所示,要從每個字符串中提取前 2 個字符和後 2 個字符,您可以應用以下公式。

從文本字符串中提取前 N 個字符

LEFT 函數可以幫助輕鬆地從 Excel 中的文本字符串中提取前 N 個字符。

通用公式

=LEFT(text_string,[num_chars])

參數

文字字串:包含要提取的字符的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
字符數:指定要提取的字符數。
Num_chars 必須大於或等於零;
如果指定的 Num_chars 大於文本字符串的長度,則返回整個文本;
如果省略 Num_chars,則假定為 1。

現在您可以應用此公式從 B 列的單元格中提取前 2 個字符。

1.選擇一個空白單元格,複製或輸入下面的公式,然後按 Enter 獲得第一個結果的關鍵。 選擇結果單元格並將其自動填充句柄向下拖動以將公式應用於其他單元格。

=LEFT(B5,2)

現在已提取範圍 B2:B5 的每個單元格中的前 10 個字符。

從文本字符串中提取最後 N 個字符

在這裡,我們應用 RIGHT 函數從 Excel 中的文本字符串中提取最後 N 個字符。

通用公式

=RIGHT(text_string,[num_chars])

參數

文字字串:包含要提取的字符的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
字符數:指定要提取的字符數。
字符數 必須大於或等於零;
如果指定 字符數 大於文本字符串的長度,則返回整個文本;
如果 字符數 省略,假定為 1。

選擇一個空白單元格,複製或輸入下面的公式,然後按 Enter 獲得結果的關鍵。 然後選擇此結果單元格並向下拖動其自動填充句柄以獲取其他結果。

=RIGHT(B5,2)

1.2 用一個神奇的工具提取第一個或最後N個字符

雖然上面的公式很簡單,但要從一長串文本字符串中提取第一個或最後 n 個字符,仍然需要從上到下拖動 AutoFill Handle,這可能會有點耗時。 這裡推薦 Kutools for Excel 提取文字 實用程序來幫助從批量文本字符串列表中提取第一個或最後 N 個字符。

1. 預先選擇要從中提取文本的文本字符串列表,然後單擊 庫工具 > 文本 > 提取文字.

2.在彈出 提取文字 對話框,您需要配置如下。

2.1) 確保您留在 按位置提取 標籤;
2.2)在 範圍 框,裡面顯示選中的範圍,可以根據需要換成其他範圍;
2.3)在 選項 部分:
如果要提取前 N 個字符,請選擇 前N個字符 單選按鈕,然後指定您將在文本框中提取的字符數。 在這種情況下,我輸入數字 2;
如果要提取最後 N 個字符,請選擇 最後N個字符 單選按鈕,然後指定您將在文本框中提取的字符數。 在這種情況下,我想從文本字符串中提取最後 2 個字符,因此我在文本框中輸入數字 2。
2.4)點擊 OK。 看截圖:

備註:要在文本字符串更改時使結果動態,您可以檢查 作為公式插入 框。

3.在接下來彈出 提取文字 對話框,選擇一個單元格以輸出提取的字符,然後單擊 OK.

然後從選定的單元格中批量提取指定的第一個或最後 N 個字符。

單擊以了解有關此功能的更多信息。

  如果您想免費試用該工具(30天), 請點擊下載,然後按照上述步驟進行操作。


2.提取某個字符/單詞之前或之後的文本

要提取某個字符或單詞之前或之後的文本,本節中的不同場景將滿足您的需求。

2.1 在第一個分隔符(一個字符)之前或之後提取文本

如下面的屏幕截圖所示,要從 B4:B10 範圍內的每個單元格中提取第一個分隔符之前或之後的文本,您可以應用以下方法之一。

2.1.1 用公式提取第一個分隔符之前的文本

應用基於 LEFT 和 FIND 函數的公式可以幫助從單元格中提取第一個分隔符之前的文本。 您可以按照以下步驟完成。

通用公式

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

參數

文字字串:要從中提取子字符串的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
分隔符:第一個分隔符,定義將從單元格中提取的文本。

選擇一個空白單元格,將下面的公式複製或輸入其中,然後按 Enter 獲得第一個結果的關鍵。 選擇第一個結果單元格並向下拖動其自動填充句柄以獲取其他單元格的第一個分隔符之前的文本。

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 用公式提取第一個分隔符後的文本

下面的公式有助於從 Excel 中的單元格中提取第一個分隔符之後的文本。

通用公式

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

參數

文字字串:要從中提取子字符串的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
分隔符: 定義將從單元格中提取的文本的第一個分隔符。
選擇一個空白單元格,將下面的公式複製或輸入其中,然後按 Enter 獲得第一個結果的關鍵。 選擇第一個結果單元格並向下拖動其自動填充句柄以獲取其他結果。

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 使用神奇的工具在第一個分隔符之前或之後提取文本

這裡強烈推薦 提取文字 的效用 Kutools for Excel。 使用此功能,您可以輕鬆地從一系列單元格中批量提取第一個分隔符之前或之後的文本。

1. 選擇要提取文本的單元格範圍,然後單擊 庫工具 > 文本 > 提取文字.

2。 在裡面 提取文字 對話框,您需要配置如下。

2.1) 留在 按位置提取 標籤;
2.2)在 範圍 框,選擇的範圍顯示在裡面,可以根據需要更改;
2.3)在 選項 部分:
要在第一個分隔符之前提取文本,請選擇 在正文之前 單選按鈕,然後在文本框中鍵入第一個分隔符;
要在第一個分隔符之後提取文本,請選擇 在正文之後 單選按鈕,然後在文本框中輸入第一個分隔符。
2.4)點擊 確定。

備註: 要在文本字符串更改時使結果動態化,您可以選中作為公式插入框。

3.然後另一個 提取文字 彈出對話框,選擇要輸出結果的單元格,點擊 確定。

然後立即從選定的單元格中提取第一個分隔符之前或之後的文本。

要了解有關此功能的更多信息,請訪問: 從 Excel 中的單元格中快速提取某些文本.

  如果您想免費試用該工具(30天), 請點擊下載,然後按照上述步驟進行操作。

2.2 在最後一個分隔符(一個字符)之前或之後提取文本

在上面的步驟中,我們學習了從單元格的第一個分隔符之前或之後提取文本的方法。 如下面的屏幕截圖所示,本節將向您展示兩個公式,用於在單元格的最後一個分隔符之前或之後提取文本。 您可以按照以下步驟完成。

2.2.1 用公式提取最後一個分隔符之前的文本

要從單元格中提取最後一個分隔符之前的文本,可以使用 LEFT 函數內部的 SEARCH、LEN 和 SUBSTITUTE 函數。

通用公式

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

參數

文字字串:要從中提取子字符串的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
分隔符: 定義將從單元格中提取的文本的最後一個分隔符。

選擇一個單元格,輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格並向下拖動其自動填充句柄以從同一列中的其他文本字符串中提取文本。

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 用公式提取最後一個分隔符後的文本

從單元格中提取最後一個分隔符之前的文本後,您可以根據需要應用下面的公式來提取最後一個分隔符之後的文本。

通用公式

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

參數

文字字串:要從中提取子字符串的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
分隔符: 定義將從單元格中提取的文本的最後一個分隔符。

選擇一個單元格,輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格並向下拖動其自動填充句柄以從同一列中的其他文本字符串中提取文本。

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 提取第n個字符後的文本

看下面的例子,在 B4:B10 範圍內有一個文本字符串列表,要從每個單元格中提取第三個字符之後的文本,您可以應用基於 MID 函數和 LEN 函數的公式。

通用公式

=MID(text_string,nth_char+1,LEN(text_string))

參數

文字字串:要從中提取子字符串的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
nth_char: 一個數字代表第n個字符,你將提取它後面的文本。

選擇一個空白單元格,將下面的公式複製或輸入其中,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格並向下拖動其自動填充句柄以獲得其他結果。

=MID(B5,3+1,LEN(B5))

2.4 從文本字符串中提取第n個單詞

假設您有如下屏幕截圖所示的文本字符串列表,並且只想從文本字符串中提取第 n 個單詞,本節提供三種方法供您完成。

2.4.1 用公式提取第n個單詞

您可以結合 TRIM、MID、SUBSTITUTE、REPT 和 LEN 函數從單元格中的文本字符串中提取第 n 個單詞。

通用公式

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

參數

文字字串:要從中提取第 n 個單詞的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
N:一個數字代表您將從文本字符串中提取的第 n 個單詞。

在這種情況下,範圍 B5:B10 包含文本字符串,D5:D10 包含表示第 n 個單詞的數字,讓我們應用此公式從文本字符串中提取第 n 個單詞。

選擇一個空白單元格,將下面的公式複製或輸入其中,然後按 Enter 獲得第一個結果的關鍵。 選擇此結果單元格並向下拖動其自動填充句柄以獲取其他單元格的第 n 個單詞。

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

備註: 您可以直接在公式中鍵入第 n 個數字,如下所示。

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 用自定義函數提取第n個單詞

除了上述公式,您還可以應用用戶定義的函數從 Excel 中的單元格中提取第 n 個單詞。

1。 按 其他 + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊 然後將下面的 VBA 複製到代碼窗口中。

VBA 代碼:從單元格中的文本字符串中提取第 n 個單詞

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3。 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

4. 返回包含要從中提取第 n 個單詞的文本字符串的工作表。 選擇一個空白單元格,複製或輸入下面的公式,然後按 Enter 獲得第 n 個單詞的關鍵。

=FindWord(B5,D5)

Or

=FindWord(B5,2)

備註: 在公式中,D5 是包含代表第 n 個單詞的數字的單元格。 或者,您可以直接將單元格引用替換為數字。

5. 選擇結果單元格並將其自動填充句柄向下拖動以從其他單元格的文本字符串中提取第 n 個單詞。

2.4.3 用神奇的工具提取第n個單詞

如果您不想手動應用上面提供的公式或用戶定義的函數,這裡推薦 Kutools for Excel's 提取單元格中的第n個單詞 公用事業。 使用此功能,您只需單擊幾下即可輕鬆地從單元格中的文本字符串中提取第 n 個單詞。

1. 選擇要放置結果的單元格,然後單擊 庫工具 > 公式助手 > 文本 > 提取 單元格中的第 n 個單詞。 看截圖:

2。 在裡面 公式助手 對話框,您需要配置如下。

2.1)在 選擇一個公式列表 盒子, 提取單元格中的第n個單詞 選項被突出顯示;
2.2)在 細胞 框,選擇一個包含要從中提取第 n 個單詞的文本字符串的單元格;
2.3)在 第N個 框,選擇包含第n個數字的單元格或根據需要直接輸入數字;
2.4)點擊 確定。

3.然後從B5單元格的文本字符串中提取第n(第二)個單詞,可以看到同時創建了一個公式。 選擇此結果單元格並向下拖動其自動填充句柄以從其他文本字符串中獲取第 n 個單詞。

單擊以了解有關此功能的更多信息。

  如果您想免費試用該工具(30天), 請點擊下載,然後按照上述步驟進行操作。

2.5 在第 n 次出現分隔符之前或之後提取文本

假設您有一個文本字符串列表,如下面的屏幕截圖所示。 要在第二次出現空格之前或之後提取文本,本節提供了兩個公式來幫助您完成。

2.5.1 在第 n 次出現分隔符之前提取文本

您可以使用 LEFT 函數以及 SUBSTITUTE 和 FIND 函數從 Excel 中的單元格中提取第 n 次出現分隔符之前的文本。

通用公式

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

參數

文字字串:要從中提取文本的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
N:一個數字代表第 n 次出現的分隔符,您將在該分隔符之前提取文本。

選擇一個單元格,複製或輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格並將其向下拖動 AutoFill Handle 以獲取列表中的其他結果。

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

備註: 在公式中,B5 是包含要從中提取文本的文本字符串的單元格; 這裡的“ ”代表一個空格,數字 2 代表第二次出現的空格。 您可以根據需要更改它們。

2.5.2 在第 n 次出現分隔符後提取文本

要在第 n 次出現分隔符後提取文本,您可以將 RIGHT 函數與 SUBSTITUTE、LEN 和 FIND 函數一起應用。

通用公式

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

參數

文字字串:要從中提取文本的文本字符串。 它可以是單元格引用或用雙引號括起來的實際文本字符串;
N:一個數字代表第 n 次出現的分隔符,您將在它之後提取文本。

現在,您可以應用此公式從 B5:B10 範圍內的每個單元格中第二次出現空格後提取文本,如下所示。

選擇一個單元格,輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格並將其向下拖動 AutoFill Handle 以獲得其他結果。

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 在換行之前或之後提取文本

假設您在 B 列中有一個訂單列表,並且您只想從每個單元格中提取日期部分和產品編號部分。 您可以使用下面的 Excel 公式來完成它。

2.6.1 用公式提取第一個換行符之前的文本

正如您在上面的屏幕截圖中看到的,日期部分位於單元格內的第一個換行符之前。 本節將演示 LEFT 函數和 SEARCH 函數,以幫助您提取單元格內第一個換行符之前的文本。

通用公式

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

參數

細胞: 要從中提取第一個換行符之前的文本的單元格。

選擇一個空白單元格,將下面的公式複製或輸入其中,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格,然後向下拖動其自動填充句柄以將此公式應用於其他單元格。

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

然後您可以看到提取範圍 B5:B8 中每個單元格中第一個換行符之前的文本,如下面的屏幕截圖所示。

備註: 在公式中,CHAR(10) 表示 Windows 上的換行符。

2.6.2 用公式提取最後一個換行符後的文本

在上一步中,我們討論瞭如何在單元格中的第一個換行符之前提取文本。 這部分將指導您如何在具有不同公式的單元格中的最後一個換行符之後提取文本。

通用公式

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

參數

細胞: 要從中提取第一個換行符之前的文本的單元格。

選擇一個空白單元格,輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格,然後向下拖動其自動填充句柄以將公式應用於其他單元格。

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

然後是產品編號。 提取列表中每個單元格的一部分,如上面的屏幕截圖所示。

備註: 在公式中,CHAR(10) 表示 Windows 上的換行符。

2.7 提取單詞之前或之後的文本

在前面的部分中,我們學習瞭如何在字符或分隔符之前或之後提取文本。 您應該怎麼做才能在整個單詞之前或之後提取文本? 本節將介紹三種方法來幫助您完成此任務。

2.7.1 用公式提取某個單詞前的文本

以下公式可幫助您在 Excel 中的單元格中提取特定單詞之前的文本。

通用公式

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

參數

細胞: 要從中提取特定單詞之前的文本的單元格。
字: 您要提取它之前的所有文本的單詞。 它可以是單元格引用或用雙引號括起來的實際文本字符串;

選擇一個空白單元格,輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格,然後向下拖動其自動填充句柄以將此公式應用於其他單元格。

在此示例中,我們將提取單詞“Excel”之前的所有文本,因此我們直接在公式中鍵入單詞並將其括在雙引號中。 或者您可以引用包含“Excel”一詞的單元格。

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

筆記:

1) 此公式區分大小寫。
2) 如果您輸入的單詞在同一個單元格中有重複,則公式僅提取第一次出現後的文本。

2.7.2 用公式提取某個單詞後的文本

要在某個單詞之後提取文本,您可以應用以下公式來完成它。

通用公式

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

參數

細胞: 您要從中提取某個單詞之後的文本的單元格。
字: 您要提取其後所有文本的單詞。 它可以是單元格引用或用雙引號括起來的實際文本字符串;

選擇一個單元格,輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇結果單元格,然後向下拖動其自動填充句柄以將此公式應用於其他單元格。

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

然後你可以看到每個單元格中“Excel”這個詞被提取出來後的所有文本,如下面的截圖所示。

筆記:

1) 此公式不區分大小寫。
2) 如果您輸入的單詞在同一個單元格中有重複,則公式僅提取第一次出現後的文本。

2.7.3 使用神奇的工具在某個單詞之前或之後提取文本

如果您覺得使用公式可能會帶來很多不便,這裡強烈推薦 提取文字 的效用 Kutools for Excel. 只需單擊幾下,此功能有助於在 Excel 中自動執行提取任務。

1。 點擊 庫工具 > 文本 > 提取文字 啟用此功能。

2。 在裡面 提取文字 對話框,進行以下設置。

2.1) 確保您在 按位置提取 標籤;
2.2)在 範圍 框,單擊 按鈕選擇要提取文本的單元格範圍;
2.3)在 選項 部分:
要提取單詞之前的所有文本,請選擇 在正文之前 單選按鈕,然後在文本框中輸入單詞;
要提取單詞後的所有文本,請選擇 在正文之後 單選按鈕,然後在文本框中輸入單詞。
2.4)點擊 OK 按鈕。 看截圖:

筆記: 如果要創建動態結果,請選中 作為公式插入 盒子。 然後當範圍內的數據發生變化時,結果會自動更新。

3.然後 提取文字 彈出對話框,您需要選擇一個單元格來輸出結果,然後單擊 OK 按鈕。

然後立即提取所選範圍內每個單元格中某個單詞之前或之後的文本。

備註: 此功能區分大小寫。

單擊以了解有關此功能的更多信息。

  如果您想免費試用該工具(30天), 請點擊下載,然後按照上述步驟進行操作。


3.字符/單詞之間的提取

如果要提取某些字符或單詞之間的文本,請嘗試以下方法。

3.1 提取兩個字符之間的文本

提取兩個字符之間的文本,這兩個字符可能是相同或不同的字符。 本節提供了幾種方法,您可以根據需要選擇其中一種。

3.1.1 用公式提取兩個相同字符之間的文本

如下圖所示,B 列中有一個文本字符串列表,如果你想從範圍內的每個單元格中提取字符“/”之間的數字部分,下面的公式可以幫到你。

選擇一個空白單元格,複製或輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇結果單元格,然後將其拖動 AutoFill Handle 以獲取列表中其他單元格的結果。

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

然後從範圍內的每個單元格中提取兩個相同字符“/”之間的文本。 看截圖:

備註:

1) B5 是您要從中提取兩個相同字符之間的文本的單元格;
2)“/” 是您要在它們之間提取文本的兩個相同字符。
您需要根據自己的數據更改這些變量。

3.1.2 用公式提取兩個不同字符之間的文本

在學習瞭如何在單元格內的兩個相同字符之間提取文本之後,我們將在這裡演示一個在兩個不同字符之間提取文本的公式。 如下圖所示,要從 B 列的每個單元格中僅提取“<”和“>”之間的電子郵件地址,您可以執行以下操作。

通用公式

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

參數

細胞: 要從中提取兩個不同字符之間的文本的單元格;
結束字符: 兩個不同字符的結束字符;
開始字符: 兩個不同字符的起始字符。

選擇一個空白單元格,複製或輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格,然後向下拖動其自動填充句柄以將此公式應用於其他單元格。

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

您可以看到只提取了指定字符之間的文本,如上面的屏幕截圖所示。

3.1.3 用一個神奇的工具提取兩個字符之間的文本

這裡強烈推薦 提取指定文本之間的字符串 的特點 Kutools for Excel 幫助您輕鬆提取 Excel 單元格中兩個相同或不同字符之間的文本。

1.選擇一個空白單元格以輸出結果,然後單擊 庫工具 > 公式助手 > 公式助手.

2。 在裡面 公式助手 對話框,進行以下設置。

2.1)檢查 篩選 框,然後輸入單詞 “提煉” 進入文本框;
2.2)在 選擇一個公式列表 框,單擊 提取指定文本之間的字符串 選項;
2.3)在 參數輸入 部分:
細胞 框,選擇要提取文本的單元格(這裡我選擇單元格B5);
起始字符 框,輸入兩個不同字符的起始字符;
結束 char(s) 框中,輸入兩個不同字符的結束字符。
2.4)點擊 確定。 見截圖:

3.然後只提取單元格B5中“<”和“>”之間的文本。 同時,已經創建了一個公式,您可以選擇此結果單元格,然後將其自動填充句柄向下拖動以從同一列表中的其他單元格中提取文本。

單擊以了解有關此功能的更多信息。

  如果您想免費試用該工具(30天), 請點擊下載,然後按照上述步驟進行操作。

3.1.4 按規則提取兩個字符(包括字符)之間的文本

如果您想在提取後保留這兩個字符,請嘗試在 Kutools for Excel 的提取文本功能中應用規則。

1。 點擊 庫工具 > 文本 > 提取文字.

2。 在裡面 提取文字 對話框,進行以下設置。

2.1)點擊 依規則提取 標籤;
2.2)在 範圍 部分,點擊 按鈕選擇要在字符之間提取文本的單元格範圍;
2.3)在 文本 框中輸入 <*>;
提示:<“和”>” 是要在它們之間提取文本的字符,* 是表示任意數量字符的通配符。 您可以根據需要輸入條件。
2.4)點擊 加入 按鈕將條件添加到 規則說明 列錶框;
2.5)點擊 Ok 按鈕。 看截圖:

3.另一個 提取文字 彈出對話框,請選擇要輸出結果的單元格,然後單擊 OK 按鈕。

然後從選定範圍內的每個單元格中批量提取指定字符(包括字符)之間的文本。

  如果您想免費試用該工具(30天), 請點擊下載,然後按照上述步驟進行操作。

3.2 提取兩個單詞之間的文本

除了提取兩個字符之間的文本外,您可能還需要提取兩個單詞之間的文本。 例如,從 B 列的每個單元格中提取兩個單詞“KTE”和“feature”之間的所有文本字符串,如下面的屏幕截圖所示。 您可以嘗試以下方法之一來完成它。

3.2.1 用公式提取兩個單詞之間的文本

您可以使用基於 MID 函數和 SEARCH 函數的公式來提取單元格內兩個單詞之間的所有文本字符串。

通用公式

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

參數

細胞: 要從中提取兩個單詞之間的所有文本字符串的單元格;
起始字: 您要提取其後所有文本字符串的兩個單詞的起始詞;
長度1: 起始詞的字符長度。
結束字: 您要提取其之前的所有文本字符串的兩個單詞的結束詞。
長度2: 起始詞的字符長度加 1。

選擇一個空白單元格,複製或輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格,然後向下拖動其自動填充句柄以將此公式應用於其他單元格。

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

備註:公式中,數字3代表單詞“KTE”的字符長度; 數字 4 表示單詞“KTE”的字符長度加 1。

您可以看到指定兩個單詞之間的所有文本字符串都是從 B 列中的每個單元格中提取的。

3.2.2 用一個神奇的工具提取兩個單詞之間的文本

對於許多 Excel 用戶來說,公式可能很難記住和處理。 在這裡,與 提取指定文本之間的字符串 的特點 Kutools for Excel,您只需單擊幾下即可輕鬆提取兩個單詞之間的文本。

1. 選擇要輸出結果的單元格,然後點擊 庫工具 > 公式助手 > 公式助手。

2。 在裡面 公式助手 對話框,您需要配置如下。

2.1)檢查 篩選 框,然後輸入單詞 “提煉” 進入文本框;
2.2)在 選擇一個公式列表 框,單擊 提取指定文本之間的字符串 選項;
2.3)在 參數輸入 部分:
細胞 框,選擇要提取文本的單元格(這裡我選擇單元格B5);
起始字符 框,輸入要提取其後所有文本字符串的兩個詞的起始詞;
結束字符 框,輸入要提取其前面所有文本字符串的兩個詞的結束詞。
2.4)點擊 確定。 見截圖:

3.然後提取單元格B5中兩個單詞“KTE”和“feature”之間的所有文本字符串。 同時,已經創建了一個公式,您可以選擇此結果單元格,然後將其自動填充句柄向下拖動以從同一列表中的其他單元格中提取文本。

  如果您想免費試用該工具(30天), 請點擊下載,然後按照上述步驟進行操作。


按位置提取數字

對於字母數字字符串的列表,可能有三種情況:

  1. 數字在正文的開頭;
  2. 數字在文末;
  3. 數字可以在文本中的任何位置.

在本節中,我們將提供可用於在上述每種情況下提取數字的不同方法。

1 從字符串左側提取數字

這部分將介紹一個公式來幫助您僅提取單元格中文本之前出現的數字。

通用公式

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

參數

細胞: 文本字符串左側要從中提取數字的單元格。

備註:

1) 如果您使用的是 Excel 2019 及更早版本,則需要按 按Ctrl + 轉移 + Enter 鍵來確認這個數組公式。
2) 如果您使用的是 Excel 365 或 Excel 2021,只需使用 Enter 鍵。

選擇一個空白單元格,輸入下面的公式,然後按 按Ctrl + 轉移 + Enter or Enter 獲得結果的關鍵。 選擇此結果單元格,然後向下拖動其自動填充句柄以獲取其他單元格的數量。

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

筆記:

1) 如果一個單元格只包含數字,則將提取整個數字。
2) 此公式僅提取文本字符串左側的數字。 如果數字存在於文本字符串的中間或末尾,它們將被忽略。

2 從字符串右側提取數字

如下面的屏幕截圖所示,要僅提取單元格中文本之後出現的數字,請嘗試以下公式。

通用公式

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

參數

細胞: 要從文本字符串的開頭提取數字的單元格。

備註:

1) 如果您使用的是 Excel 2019 及更早版本,則需要按 按Ctrl + 轉移 + Enter 鍵來確認這個數組公式。
2) 如果您使用的是 Excel 365 或 Excel 2021,只需按 Enter 鍵確認此公式。

選擇一個空白單元格,輸入下面的公式,然後按 按Ctrl + 轉移 + Enter or Enter 獲得結果的關鍵。 選擇此結果單元格,然後向下拖動其自動填充句柄以獲取其他單元格的數量。

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

筆記:

1) 如果一個單元格只包含數字,則將提取整個數字。
2) 此公式僅提取文本字符串右側的數字。 如果數字存在於文本字符串的中間或開頭,它們將被忽略。

3. 提取文本字符串任意位置的所有數字

上述方法有助於僅從文本字符串的左側或右側提取數字。 如果您想從文本字符串中的任何位置提取所有數字,這裡我們提供三種方法供您完成。

3.1 用公式從字符串中任意位置提取所有數字

您可以應用以下公式從 Excel 中文本字符串的任何位置提取所有數字。

1.選擇一個空白單元格,複製或輸入下面的公式,然後按 Enter 鍵從單元格 B5 中獲取所有數字。

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. 選擇結果單元格,然後將其自動填充句柄向下拖動以獲取所有其他單元格的數量。

3.2 用VBA從字符串中任意位置提取所有數字

上面的公式對於許多 Excel 用戶來說太長太複雜了。 實際上,您可以運行 VBA 腳本來自動執行 Excel 中的任務。 您可以執行以下操作。

1。 按 其他 + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2.在開幕 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊。 然後將下面的 VBA 複製到模塊代碼窗口中。

VBA代碼:從文本字符串中的任何位置提取所有數字

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3。 按 F5 鍵來運行代碼。 在開幕 KutoolsforExcel 對話框中,選擇要從每個單元格中提取所有數字的單元格區域,然後單擊 OK 按鈕。

4.然後另一個 KutoolsforExcel 對話框彈出。 在此對話框中,選擇目標單元格並單擊 確定。

然後從選定範圍內的每個單元格中批量提取所有數字。


4.提取特定文本後的數字

如下圖所示,要提取特定文本“No.”之後的任何數字,本節提供了兩種方法來幫助您完成。

4.1 用公式提取特定文本後的數字

您可以應用以下公式來提取 Excel 單元格中特定文本之後的數字。

通用公式:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

參數

細胞: 您要從中提取特定文本後數字的單元格;
文本: 您要在其後提取數字的文本。

選擇一個空白單元格,複製或輸入下面的公式,然後按 Enter 獲得結果的關鍵。 選擇此結果單元格,然後向下拖動其自動填充句柄以將此公式應用於其他單元格。

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

筆記:

1) 如果單元格不包含特定文本,則公式將返回#N/A。
2) 公式區分大小寫。

4.2 使用自定義函數提取特定文本後的數字

以下用戶定義的函數還可以幫助提取單元格中特定文本之後的數字。 請執行以下操作。

1。 按 其他 + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊 然後將下面的 VBA 代碼複製到模塊代碼窗口中。

VBA代碼:在單元格中的特定文本之後提取數字

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3。 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

4. 選擇一個單元格,輸入下面的公式,然後按 Enter 鑰匙。 選擇此結果單元格,然後向下拖動其自動填充句柄以將此公式應用於其他單元格。

=GetNumberAfterTheChar(B5,"No. ")

筆記:

1)在這個公式中,B5是你要提取數字的單元格,“No. ” 是您要提取其後數字的特定文本。 您可以根據需要更改它們。
2) 如果具體文字和數字之間有分隔符,請在文字末尾加上分隔符。 在這種情況下,我在文本“No.”之後添加了一個空格,最終顯示為“No.”。 ”。
3) 此方法不區分大小寫;
4) 如果單元格不包含特定文本,則公式將返回空白結果。

相關文章:

Excel 教程:拆分文本、數字和日期單元格(分成多列)
本教程分為三個部分:拆分文本單元格、拆分數字單元格和拆分日期單元格。 每個部分都提供了不同的示例,以幫助您了解在遇到相同問題時如何處理拆分作業。
點擊了解更多...

Excel將文本和數字添加到單元格的指定位置
在 Excel 中,向單元格添加文本或數字是一項非常常見的工作。 例如在名稱之間添加空格,在單元格中添加前綴或後綴,在社交號碼中添加破折號。 在本教程中,它列出了 Excel 中幾乎所有的添加場景,並為您提供了相應的方法。
點擊了解更多...

Excel從文本字符串中刪除字符、單詞、數字
假設您有一長串包含字符、數字或其他特定符號的文本字符串。 在某些情況下,您可能需要根據位置刪除一些字符,例如從文本字符串中的右側、左側或中間,或者從字符串列表中刪除一些不需要的字符、數字。 一一尋找解決方案會讓你頭疼,本教程收集了Excel中去除字符、單詞或數字的各種方法。
點擊了解更多...


最佳辦公效率工具

Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過300種強大功能。 支持Office / Excel 2007-2019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。
kte選項卡201905

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

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 每天將您的工作效率提高50%,並減少數百次鼠標單擊!
officetab底部
按評論排序
留言 (0)
還沒有評分。 成為第一位評論!
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0  字符
推薦地點