從文字數字無規律混合的Excel列中提出數字, 只需此函式重複兩次

首頁 > 科技

從文字數字無規律混合的Excel列中提出數字, 只需此函式重複兩次

來源：戲說健康 釋出時間：2023-12-12 18:44

如何從文字和數字混合的單元格中提取出所有數字？如果文字和數字出現的位置和次數有規律，那比較好辦，Ctrl+E 和 Power Query 都有智慧聯想功能。

如果毫無任何規律，那麼今天這兩個函式就能大顯身手了。

案例：

將下圖 1 中每個單元格中的所有數字和小數點全都提取出來，如果同一個單元格中的數字或小數點無論是否連續，提取出來都要放在同一個單元格中。

效果如下圖 2 所示。

解決方案：

1. 找一個空白列作為輔助列，在其中列出要提取出來的所有數字元素。

* D2 單元格中是個小數點。

2. 在 B2 單元格中輸入以下公式 --> 下拉複製公式：

=IFERROR(TEXTJOIN("",TRUE,TEXTSPLIT(A2,TEXTSPLIT(A2,$D$2:$D$12,,TRUE),,TRUE)),"")

公式中用到了兩個 365 函式 TEXTSPLIT 和 TEXTJOIN，那我們先來學習一下這兩個函式，再來解釋公式。

TEXTSPLIT 函式

作用：使用列或行分隔符拆分文字字串，允許跨列拆分或按行向下拆分；

語法：TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

text：必需，要拆分的文字；

col_delimiter：標記跨列溢位文字的點的文字；

[row_delimiter]：可選，標記向下溢位文字行的點的文字；

[ignore_empty]：可選，指定為 TRUE 可以忽略連續分隔符；預設為 FALSE，將建立一個空單元格；

[match_mode]：可選，指定 1 則不區分大小寫的；預設為 0，會區分大小寫；

[pad_with]：可選，用於填充結果的值；預設值為 #N/A。

TEXTJOIN 函式

作用：將多個區域和/或字串的文字組合起來，其中包括要組合的各文字值之間指定的分隔符；

語法：TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

delimiter：必需，文字字串，或空值，或用雙引號引起來的一個或多個字元，或對有效文字字串的引用；數字也會被視為文字；

ignore_empty：必需，如果為 TRUE，則忽略空白單元格；

text1：必需，要聯接的文字項；

[text2, ...]：可選，要聯接的其他文字項。

公式釋義：

照例，要從最裡面的公式開始，一層層向外分解。

TEXTSPLIT(A2,$D$2:$D$12,,TRUE)：

將 A2 單元格按 $D$2:$D$12 區域的分隔符拆分到不同的列；

也就是將數字和小數點刪除，僅提取出文字；

TEXTSPLIT(A2,...,,TRUE)：

在剛才的公式外面再套一個 TEXTSPLIT 函式，目的是用剛才提出來的中英文或字元作為分隔符，將 A2 單元格按列分開；

相當於上述公式的反轉版，留存數字和小數點，將其他全部刪除；

之所以要用兩次 TEXTSPLIT 來實現，是因為在輔助列中沒法列舉窮盡所有文字和字元，所以採用的辦法是先刪除所有要提取的數字，剩下的就是不需要提取的；再一次用公式將這些不需要的全部刪除；

TEXTJOIN("",TRUE,...)：上面提取出的數字如果不是連續的，就會放在不同的列中，要將它們合併在同一個單元格中，就要用 TEXTJOIN 將結果聯接起來，數字之間不需要分隔符；

IFERROR(...,"")：最後在公式外面套上 iferror 函式，不顯示錯誤值

* 公式中的輔助區域 $D$2:$D$12 要絕對引用。

如何從文字和數字混合的單元格中提取出所有數字？如果文字和數字出現的位置和次數有規律，那比較好辦，Ctrl+E 和 Power Query 都有智慧聯想功能。

如果毫無任何規律，那麼今天這兩個函式就能大顯身手了。

案例：

將下圖 1 中每個單元格中的所有數字和小數點全都提取出來，如果同一個單元格中的數字或小數點無論是否連續，提取出來都要放在同一個單元格中。

效果如下圖 2 所示。

上一篇：夸人見識多的... 下一篇：Xiaomi Mi Ba...
猜你喜歡
熱門閱讀
同類推薦

本站內容收集於網路，如有侵權，請聯絡我們處理

IT-TOOLS | PDF Tools | TinyImage | 詩韻中華 | OG Image Generator

今日必看 | 聯絡我們 | sitemap | 簡體中文