關(guān)注公。眾。號【秋葉Excel】，后臺回復(fù)關(guān)鍵詞【提取】即可下載本文案例文件！

大家好鴨，我是秋葉 Excel 學(xué)習(xí)班里，沉迷于學(xué)習(xí) Excel 的小爽~

最近啊，我有了一個小粉絲——遠(yuǎn)歌同學(xué)（插會兒腰）。

某天我在玩電腦的時候，微信突然叮~ 了兩聲，仔細(xì)一看，原來是可愛滴遠(yuǎn)歌：

應(yīng)遠(yuǎn)歌的求助，今天我就來給大家簡單介紹一下——利用函數(shù)，快速提取單元格中的數(shù)字！

老規(guī)矩，練習(xí)文件放文末啦，記得看到最后領(lǐng)取~

01?數(shù)字與漢字

◆?數(shù)字與漢字之間有分隔符?◆

【Ctrl E】&分列的方法大家都很熟悉啦，這里就不再贅述~

這里我們還可以使用?find 函數(shù)查找分隔符。

在 C2 單元格輸入公式如下，最后下拉填充就搞定啦。

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

小解析：利用 find 函數(shù)找到分隔符所在的位置，再用 left 函數(shù)從左邊開始提取數(shù)字。

◆?數(shù)字與漢字之間沒有分隔符?◆

? 數(shù)字在漢字左側(cè)。

和上文一樣，也可以用快捷鍵【Ctrl E】進(jìn)行分列。

我們還可以借助?lenb+len 函數(shù)，獲取位置信息、提取數(shù)字。

如下圖中，len 函數(shù)將英文作為 1 個字節(jié)，而 lenb 函數(shù)將漢字作為 2 個字節(jié)。

因此，我們在 F2 單元格中輸入以下公式，下拉填充即可：

=LEFT(E2,LEN(E2)*2-LENB(E2))

小解析：

LEN(E2)*2-LENB(E2) 就是獲取左邊數(shù)字的個數(shù)，搭配 left 函數(shù)截取左邊的數(shù)字。

??數(shù)字在漢字右側(cè)。

當(dāng)數(shù)字在漢字右邊時，我們可以用函數(shù) len+lenb+mid。

輸入公式如下：

=MID(H2,LENB(H2)-LEN(H2)+1,99)

小解析：

LENB(H2)-LEN(H2)主要是獲得左邊文本的個數(shù)，加上 1 后就是首個數(shù)字出現(xiàn)的位數(shù)。

從首個位置開始截取到 99 位（具體提取的位數(shù)只要大于等于數(shù)字的最大位數(shù)即可）。

??數(shù)字在漢字中間。

需要利用 lookup 函數(shù)配合數(shù)組公式來做。

公式如下：

=-LOOKUP(1,-MIDB(K2,SEARCHB("?",K2),ROW($1:$15)))

小解析：

這里是利用 SEARCHB 函數(shù)的通配符查找功能，查找字符串中的數(shù)字。

由于公式是一個數(shù)組公式，需要按【Ctrl＋Shift＋Enter】進(jìn)行數(shù)組運(yùn)算。

02?數(shù)字與字母漢字混合

一旦涉及到數(shù)字，字母和漢字一起混合的情況，我們就需要使用復(fù)雜數(shù)組公式了。

數(shù)組公式需要按住進(jìn)行執(zhí)行數(shù)組運(yùn)算，公式左右兩側(cè)會出現(xiàn)大括號；

如果你是最新的預(yù)覽版 Office 365，直接按 Enter 就可以。

◆?提取在左右側(cè)的連續(xù)數(shù)字?◆

??數(shù)字在最右側(cè)。

=-LOOKUP(1,-RIGHT(O2,ROW($1:$15)))

??數(shù)字在最左側(cè)。

=-LOOKUP(1,-LEFT(R2,ROW($1:$15)))

小解析：

首先使用 right 函數(shù)，從右到左分別截取長度為 1 到 15 的子字符串，再利用取負(fù)運(yùn)算，將文本轉(zhuǎn)換為錯誤值，將數(shù)值轉(zhuǎn)換為負(fù)數(shù)；

然后利用 lookup 函數(shù)獲取數(shù)組的最后一個值，最后將負(fù)數(shù)取負(fù)轉(zhuǎn)為正數(shù)。

從左側(cè)提取的數(shù)字原理，跟從右側(cè)提取的原理相同。

◆?數(shù)字在任意位置?◆

??借助 lookup 函數(shù)。

=-LOOKUP(0,-MID(K2,MIN(FIND(ROW($1:$10)-1,K2&1/17)),ROW($1:$15)))

小解析：

? 首先利用 ROW 函數(shù)構(gòu)造 0-9 的數(shù)字?jǐn)?shù)組：

ROW($1:$10)-1

? 「1/17」等于 0.0588235294117647，它是一個包含 0-9 所有數(shù)字的值，連接在文本的尾部是避免 FIND 函數(shù)在查找數(shù)字時，因文本缺少相應(yīng)數(shù)字而返回錯誤值。

? 用 FIND 函數(shù)查找 10 個數(shù)字在文本中的位置，利用 MIN 函數(shù)返回文本中出現(xiàn)數(shù)字的最小位置，即可以得到首個數(shù)字的位置。

? 利用 MID 函數(shù)從首個數(shù)字位置開始，依次向右截取長度為 1~15 的 15 個子字符串，加上負(fù)號，將數(shù)字轉(zhuǎn)化為負(fù)數(shù)，文本轉(zhuǎn)化為錯誤值。

最后利用 LOOKUP 函數(shù)返回最大的數(shù)字，再使用負(fù)號將負(fù)數(shù)轉(zhuǎn)化為正數(shù)。

? 借助 max 函數(shù)（只能提取整數(shù)）。

公式如下：

=MAX(IFERROR(--RIGHT(LEFT(K20,ROW($1:$13)),COLUMN($A:$D)),0))

小解析：

? 先用 len 函數(shù)確定字符串中最長的個數(shù)。

row(1:13)——13 這個數(shù)只要大于字符串中最長的個數(shù)即可，目的是是后面一個個子字符的截取。

??LEFT(K20,ROW($1:$13)?——從首個字符開始依次從右截取 1~13 個子字符串。

??column(a:d)——a:d 取決于字符串中數(shù)字的最大位數(shù)。

??RIGHT(LEFT(K20,ROW($1:$13)),COLUMN($A:$D)——從左開始向右依次截取 1~13 個子字符，再從此數(shù)組的基礎(chǔ)上從右開始依次向左截取 1~4 個子字符，此時是一個 13*4 的數(shù)組。

? 公式前加--，是為了將文本轉(zhuǎn)為錯誤值。

? 將錯誤值轉(zhuǎn)換為 0，從數(shù)組選出最大值，即可獲得最大數(shù)字。

嗶哩吧啦講了一段，我問遠(yuǎn)歌：

關(guān)于提取單元格中的數(shù)字，你一般是怎么做的？歡迎留言，一起交流~

最后附上函數(shù)提取數(shù)字の練習(xí)文件.xlsx，關(guān)注公。眾。號【秋葉Excel】，后臺回復(fù)關(guān)鍵詞【提取】即可下載！