SurDA

今天再分享一例 Excel 取单元格连续数字 公式 .

此公式不受数字起始位置限制,无论是在开头,中间或结尾均可自动取的单元格中的数字.公式如下:

=LOOKUP(9E+307,–MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

CTRL+SHIFT+SHIFT 数组公式

公式解读:

1. 起关键作用的是蓝色部分公式,其功效为定位数字开始位数,我称其为"样板公式".
2. LOOKUP(9E+307,…… 遍历使用数组生效
3. 9E+307 为 Excel 认为的最大值

想来这已是第四次分享 Excel 取数值 方法,之前写过三篇:

基础篇:Excel 数,字分享

进阶篇:Excel 数,字分离二(VBA)

分享篇:再分享一个Excel通用取数字公式

竞赛篇:Excel 数,字分离三 通用公式 分段取数值

今天正好做个总结,以供后效.Excel中数字分享无非四种情况：(以下假定字串都放置在A1单元格)

一.数字在前,文本在后. 形如 " 13777777777王十六 " ,取数字公式有:

• =-LOOKUP(1,-LEFT(A1,COLUMN(1:1)))
• =LEFT(A1,2*LEN(A1)-LENB(A1))

二.数字在后,文本在前. 形如 " 王十六13777777777 ",取数字公式有:

• =-LOOKUP(1,-RIGHT(A1,COLUMN(1:1)))
• =RIGHT(A1,2*LEN(A1)-LENB(A1))

三.数字在中间. 形如 " 王十六13777777777李九五 ",取数字公式:

• =LOOKUP(9E+307,–MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))   数组公式 CTRL+SHIFT+ENTER

四.字符串中有多处连续数字. 形如 " 王十六13777777777李九五13888888888 " ,取数字公式:

• =TEXT(SUM(MID("0"&A1,LARGE(ISNUMBER(-MID(A1,COLUMN(2:2),1))*COLUMN(2:2),COLUMN(2:2))+1,1)*10^COLUMN(2:2))/10,REPT(0,SUM(ISNUMBER(-MID(A1,COLUMN(2:2),1))*1)))

数组公式 CTRL+SHIFT+ENTER  ,此公式为连续取值,最终取值效果为 "1377777777713888888888".需要注意的是,公式实际是运用了"加法"运算,当最终加数超过一个科学计量单位(既超过15位),第16位开始四舍五入,取数字也就不精确了.

• =MID(LEFT("a"&$A2,SMALL((MMULT(N(MID($A2,ROW($1:98)+{0,1},1)<"A"),{-1;1})>-1)/1%+ROW($2:99),COLUMN(A2))),SMALL((MMULT(N(MID("a"&$A2,ROW($1:98)+{0,1},1)>"9"),{-1;1})>-1)/1%+ROW($2:99),COLUMN(A2)),15)  

数组公式 CTRL+SHIFT+ENTER 注:此公式为每段取值,请向右填充公式.取得数字分别存储在独立单元格中.

当然以上四种取数字公式有变通法,Surda 只列举最通用,最简单的.