☰

如何在Excel中選擇儲存格中的第一個數字？

tag-icon • tag-icon microsoft-excel microsoft-excel-2013 string-manipulation
如何在Excel中選擇儲存格中的第一個數字？

我有一個表，在單元格中存儲數字和一些文本，我想根據數字進行算術運算。所以我的桌子看起來像這樣：

Number1       | Number2       | Product
2             | 3             | 6
3 (some text) | 40            | 120
4             | 5 (here too)  | 20

因此，在存在這些難看的文字的情況下，我仍然希望能夠自動產生第 3 列。我想我需要一個將子字串解析為數字的函數，直到第一個非數字字元。我可以用 Excel 完成此操作嗎？

附：數字的位數可以多於 1 位。可以安全地假設它們是整數，但很高興看到一個也適用於浮點的解決方案:)

謝謝，

答案1

要取得數字（如果它們位於字串的開頭），我們可以使用以下命令：

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

作為我們的基本公式，這將找到數字的結尾並將其作為 MID() 函數的結尾返回。

這裡發生了很多事情：

SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))當這部分迭代數字時，它會將每個數字的最後一個實例替換為}}}。

SUBSTITUTE 的第三個標準是實例。我們找到帶有 的實例數LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))。它會遍歷數字並一次將每個數字替換為空。然後它會找到原始字串和新字串的長度差異。

因此，在 A2 的情況下，當它迭代到 時，2它會找到 2，並且外部 Substitute 將最後一個替換為}}}。這只是一個臨時佔位符。

聚合函數是一個多功能函數。 14 告訴我們正在使用的函數Large()。告訴6函數忽略錯誤。這很重要，因為許多迭代不會找到任何內容並傳回錯誤。

最後的1告訴函數我們希望從搜尋函數中獲得最高的返回值，該函數搜尋}}}透過迭代放置在每個數字的最後一個實例上的那些臨時值。

因此聚合返回找到的最大數量。我們將其傳遞給 Mid 函數中的長度標準。

現在我們已經找到了字串前面的數字。

因此，我們可以將其中兩個相乘以獲得所需的輸出（任何數學函數都會將傳回的字串轉換為數字）：

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

在此輸入影像描述

一個警告聚合函數是在 Excel 2010 中引入的。

如果您有舊版本，則需要使用這個較長的公式：

=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))

它的作用與上面的大致相同，接受它必須在找到最大值之前先測試錯誤。

答案2

假設數字始終與任何文字之間以空格分隔，您可以使用類似的內容。假設您的範例的資料從 A2 開始，因此 C2 中的第一個結果通常如下所示：

=A2*B2

我們需要用一個公式替換簡單的單元格引用，如果來源單元格只有數字，則將其視為數字，但如果還有文本，則提取數字。所以 C2 將變為：

=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))

如果來源儲存格不是數字，它將尋找第一個空格並傳回其前面的字元。從技術上講，它返回的是包含數字的文字值，但 Excel 足夠智能，可以在計算乘積時將其視為數字（無需將其嵌套在另一個函數中以將其轉換為數字）。請注意，如果數字是浮點數，這仍然有效。

警告：這依賴於「乾淨」的來源數據，即數字條目或以數字開頭，後跟空格的文字條目。如果存在不符合該要求的儲存格，您將需要清理資料或包含某種形式的錯誤檢查。最好的解決方案是拆分兩個資料列，以便任何文字都位於單獨的儲存格中。

相關內容