Excel公式練習17：提取字元串中的數字

07-02

本次的練習是：單元格中的數據包含文本和數字（如圖1），如何使用公式提取出該單元格中的數字？

圖1

先不看答案，自已動手試一試。

公式思路

先找到字元串文本中第1個數字出現的位置，然後取出從該位置起的全部數據。

公式

數組公式如下：

=1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:10),1)),0),255)

公式解析

首先，公式：

MID(A1,ROW(1:10),1)

將單元格A1中的數據轉換成一個數組：{「E」;」x」;」c」;」e」;」l」;」2」;」0」;」1」;」7」;」」}。

我們假設單元格A1中的數據長度不會超過10個。

然後，將數組乘以1，即公式：

1*MID(A1,ROW(1:10),1)

得到數組{#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!;2;0;1;7; #VALUE!}。

接下來，使用IFERROR函數，若數組中是錯誤值則轉換成TRUE，否則為FALSE，即公式：

ISERROR(1*MID(A1,ROW(1:10),1))

得到數組{TRUE; TRUE; TRUE; TRUE; TRUE;FALSE; FALSE; FALSE; FALSE;TRUE}。

使用MATCH函數，查找數組中第一個FALSE出現的位置，即

MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:10),1)),0)

得到值6，即字元串中的第6個字元開始出現數字。此時的公式為=1*MID(「Excel2017」,6,256)

即從字元串「Excel2017」的第6位開始提取256個字元數據，從而取出字元「2017」，然後將其乘以1，轉換成數字。

小結

使用MID函數，可以實現數據分離。

數字與文本相乘將產生錯誤值。

MATCH函數精確查找指定值第1次出現的位置。

數字與數字形式的文本相乘，將其轉換為數字。

下期預告

Excel公式練習18：獲取每行中第一個非空單元格

如何使用公式獲取每行中第一個非空單元格？例如下圖所示工作表，要求使用公式根據上半部分的表格求各項目的開工日期。

也就是說，要求出單元格區域B2:G6的每行中第一個非空單元格對應的B1:G1中的日期。

本文屬原創文章，轉載請聯繫我（xhdsxfjy@163.com）或者註明出處。

歡迎在下面留言，完善本文內容，讓更多的人學到更完美的知識。