從 Excel 中的字符串中提取數字（使用公式或 VBA）

0 Shares

Excel 中沒有內置函數可以從單元格中的字符串中提取數字（反之亦然 – 刪除數字部分並從字母數字字符串中提取文本部分）。

但是，這可以使用 Excel 函數或一些簡單的 VBA 代碼來完成。

讓我先告訴你我在說什麼。

假設您有一個如下所示的數據集，並且您想從字符串中提取數字（如下所示）：

您選擇的方法還取決於您使用的 Excel 版本：

對於 Excel 2016 之前的版本，您需要使用稍長的公式
對於 Excel 2016，您可以使用新引入的 TEXTJOIN 函數
VBA方法可用於所有版本的Excel

Contents

1 從 Excel 中的字符串中提取數字（Excel 2016 公式）
2 從 Excel 中的字符串中提取數字（適用於 Excel 2013/2010/2007）
3 使用 VBA 在 Excel 中分隔文本和數字
- 3.1 從 Excel 中的字符串中提取數字（使用 VBA）
- 3.2 從 Excel 中的字符串中提取文本（使用 VBA）
4 哦，您好很高興認識你。

從 Excel 中的字符串中提取數字（Excel 2016 公式）

此公式僅適用於 Excel 2016，因為它使用新引入的 TEXTJOIN 函數。

此外，此公式可以提取文本字符串開頭、結尾或中間的數字。

請注意，本節中介紹的 TEXTJOIN 公式會將所有數字字符放在一起。例如，如果文本是“10 張門票的價格是 200 美元”，則結果為 10200。

假設您有如下所示的數據集，並且您想從每個單元格中的字符串中提取數字：

下面的公式將為您提供 Excel 中字符串的數字部分。

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

這是一個數組公式，因此您需要使用“ Control + Shift + Enter ”而不是使用 Enter。

如果文本字符串中沒有數字，則此公式將返回空白（空字符串）。

相關問題購買二手顯示器前的檢查清單

這個公式是如何工作的？

讓我打破這個公式並嘗試解釋它是如何工作的：

ROW(INDIRECT(“1:”&LEN(A2))) – 這部分公式將給出從一個開始的一系列數字。公式中的 LEN 函數返回字符串中的字符總數。在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1) – 這部分公式將返回 #VALUE 數組！基於字符串的錯誤或數字。字符串中的所有文本字符都變為#VALUE！錯誤和所有數值保持原樣。這發生在我們將 MID 函數乘以 1 時。
IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – 使用 IFERROR 函數時，會刪除所有 #VALUE！錯誤，只有數字會保留。這部分的輸出看起來像這樣 – {“”;””;””;””;””;””;””;””;””;””;””;””;””; ””;””;””;1;0;0}
=TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)) – TEXTJOIN 函數現在簡單地組合字符串字符仍然存在（僅是數字）並忽略空字符串。

專業提示：如果要檢查部分公式的輸出，請選擇單元格，按 F2 進入編輯模式，選擇要輸出的公式部分，然後按 F9。您將立即看到結果。然後記得按 Control + Z 或按 Escape 鍵。不要按回車鍵。

您還可以使用相同的邏輯從字母數字字符串中提取文本部分。下面是從字符串中獲取文本部分的公式：

=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))

這個公式的一個小變化是IF函數用於檢查我們從MID函數得到的數組是否有錯誤。如果是錯誤，則保留該值，否則將其替換為空白。

然後使用 TEXTJOIN 來組合所有的文本字符。

注意：雖然這個公式很有效，但它使用了一個 volatile 函數（INDIRECT 函數）。這意味著，如果您將其與龐大的數據集一起使用，可能需要一些時間才能為您提供結果。最好在 Excel 中使用此公式之前創建備份。

從 Excel 中的字符串中提取數字（適用於 Excel 2013/2010/2007）

如果您有 Excel 2013. 2010. 或 2007，則不能使用 TEXTJOIN 公式，因此您必須使用複雜的公式來完成此操作。

相關問題電腦網絡術語大全──深入了解計算機世界的詞彙

假設您有一個如下所示的數據集，並且您想要提取每個單元格中字符串中的所有數字。

下面的公式將完成此操作：

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, 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),"")

如果文本字符串中沒有數字，則此公式將返回空白（空字符串）。

雖然這是一個數組公式，但您不需要使用“Control-Shift-Enter”來使用它。一個簡單的輸入適用於這個公式。

這個公式的功勞歸功於令人驚嘆的Excel 先生論壇。

同樣，無論位置如何，此公式都將提取字符串中的所有數字。例如，如果文本是“10 張門票的價格是 200 美元”，則結果為 10200。

使用 VBA 在 Excel 中分隔文本和數字

如果您經常需要分離文本和數字（或從文本中提取數字），您也可以使用 VBA 方法。

您需要做的就是使用簡單的 VBA 代碼在 Excel 中創建自定義的用戶定義函數 (UDF)，然後使用該 VBA 公式而不是使用冗長而復雜的公式。

讓我向您展示如何在 VBA 中創建兩個公式——一個用於提取數字，一個用於從字符串中提取文本。

從 Excel 中的字符串中提取數字（使用 VBA）

在這一部分中，我將向您展示如何創建自定義函數以僅從字符串中獲取數字部分。

下面是我們將用來創建這個自定義函數的 VBA 代碼：

Function GetNumeric(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function

以下是創建此函數然後在工作表中使用它的步驟：

相關問題如何將照片上傳到 Google 相冊 - 2 種簡單方法

現在，您將能夠在工作表中使用 GetText 函數。由於我們已經在代碼本身中完成了所有繁重的工作，因此您需要做的就是使用公式 =GetNumeric(A2)。

這將立即為您提供字符串的數字部分。

請注意，由於工作簿現在包含 VBA 代碼，因此您需要使用 .xls 或 .xlsm 擴展名保存它。

如果您必須經常使用此公式，您也可以將其保存到您的個人宏工作簿中。這將允許您在您使用的任何 Excel 工作簿中使用此自定義公式。

從 Excel 中的字符串中提取文本（使用 VBA）

在這一部分中，我將向您展示如何創建自定義函數以僅從字符串中獲取文本部分。

下面是我們將用來創建這個自定義函數的 VBA 代碼：

Function GetText(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetText = Result
End Function

以下是創建此函數然後在工作表中使用它的步驟：

現在，您將能夠在工作表中使用 GetNumeric 函數。由於我們已經在代碼本身中完成了所有繁重的工作，因此您需要做的就是使用公式 =GetText(A2)。

這將立即為您提供字符串的數字部分。