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

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

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

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

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

假設您有一個如下所示的數據集,並且您想從字符串中提取數字(如下所示):

從 Excel 中的字符串中提取數字 - 數據

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

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

從 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。

如果文本字符串中沒有數字,則此公式將返回空白(空字符串)。

相關問題  Windows 藍牙不工作 - 10、8.1 和 7 [解決了]

這個公式是如何工作的?

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

  • 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 公式,因此您必須使用複雜的公式來完成此操作。

相關問題  如何使用 Excel 公式查找列表中最後一次出現的項目

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

從 Excel 中的字符串中提取數字 - 數據

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

=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。

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

使用 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

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

相關問題  如何在 Excel 中創建自定義列表(簡單的分步指南)

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

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

使用自定義 VBA 函數僅從 Excel 中的字符串中獲取數字部分

請注意,由於工作簿現在包含 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)。

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

使用自定義 VBA 函數僅從 Excel 中的字符串中獲取文本部分

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

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

哦,您好 👋很高興認識你。

訂閱我們的電子報,定期發送很棒的科技內到您的郵

Post Comment