excel字符串設置

      網友投稿 623 2025-03-31

      Excel字符串設置

      上篇文章解了提取位于字符串開頭的數字的公式技術,本文研究從字符串開頭提取數字的技術:

      1. 這些數字是連續的

      2. 這些連續的數字位于字符串的末尾

      3. 想要的結果是將這些連續的數字返回到單個單元格

      與上篇文章一樣,對于下面研究的每種解決方案,我們需要在兩種不同的情況下測試其健全性:

      1. 字符串中除末尾外其他地方沒有數字的情況,例如ABC456。

      2. 字符串中除末尾外其他地方也有數字,要么在開頭,要么在中間,例如123ABC456或ABC123DEF456。

      MIN與FIND

      公式1:

      =0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1& 1/17)),LEN(A1))

      如果單元格A1中的內容為“ABC456”,那么上述公式1返回“456”。其解析過程如下:

      =0+MID(“ABC456”,MIN(FIND({0,1,2,3,4,5,6,7,8,9},“ABC456” & 1/17)),LEN(“ABC456”))

      在A1后面添加1/17與在其后面添加字符串“0123456789”效果相同。主要是必須將A1與包含0到9的所有數字的字符串連接起來,以防止MIN函數返回錯誤。因為如果A1不包含0-9的所有數字,那么FIND函數的返回值至少有一個是錯誤值,由于MIN函數不會忽略錯誤值,會導致整個公式的結果也是一個錯誤值。通過首先在A1中的字符串末尾添加一個適當的值(例如1/17、3^45,“0123456789”),可以保證該字符串將包含至少一個0-9中的每個數字(因此FIND函數的所有10個返回值都是非錯誤值),同時通過將字符串放在A1的末尾確保我們不會影響MIN函數所需的輸出。

      這樣,現在上述公式轉換為:

      =0+MID(“ABC456”,MIN(FIND({0,1,2,3,4,5,6,7,8,9},”ABC456″&0.0588235294117647)),LEN(“ABC456”))

      可能你還不知道,在某些公式結構中,MIN函數具有強制返回數組的能力,且無需按Ctrl+Shift+Enter組合鍵。本例就屬于這種情況,因此FIND函數將對傳遞給它的所有10個值進行操作,而不僅僅是對第一個值進行操作。因此,上述公式轉換為:

      =0+MID(“ABC456”,MIN({7,19,13,14,4,5,6,21,11,17}),LEN(“ABC456”))

      MIN函數返回字符串中數字開始的位置4,因此,上述公式轉換為:

      =0+MID(“ABC456”,4,LEN(“ABC456”))

      注意,MID函數(以及RIGHT函數和LEFT函數)具有一些有用的特性,當傳遞的參數num_chars的值大于要提取的實際字符串的長度時不會出錯。也就是說,如果該參數的值將導致全部或部分返回值超出字符串末尾的情況時,則這些函數僅返回從指定位置到字符串末尾的那些字符。這里將參數num_chars的值指定為LEN(A1),因為沒有子字符串的長度會大于字符串自身的情況。這樣,上述公式轉換為:

      =0+MID(“ABC456″,4,6)

      得到結果:

      456

      下面我們看看如果單元格A1中的字符串包含其它數字例如123ABC456,公式1是否有效。此時公式1轉換為:

      =0+MID(“123ABC456“,MIN(FIND({0,1,2,3,4,5,6,7,8,9},”123ABC4560.0588235294117647”)),LEN(A1))

      轉換為:

      =0+MID(“123ABC456“,MIN({10,1,2,3,7,8,9,24,14,20}),LEN(A1))

      轉換為:

      =0+MID(“123ABC456“,1,LEN(“123ABC456“))

      轉換為:

      =0+MID(“123ABC456“,1,9)

      由于字符串中有其它數字的影響,導致找到的傳遞到MID函數的起始位置變化,因此結果為:

      #VALUE!

      LOOKUP與RIGHT

      公式2:

      =-LOOKUP(0,-RIGHT(A1,ROW(INDIRECT(“1:”& LEN(A1)))))

      這個公式與上一篇文章《Excel從字符串中提取數字——數字位于字符串開頭》中的公式1相似,只是使用RIGHT函數代替了LEFT函數。

      當然,如果單元格A1中的字符串與“JAN18”、“123E4”類似,則不會返回正確的結果,詳細原因參見上篇文章。

      RIGHT、MATCH與ISNUMBER

      公式3:

      =0+RIGHT(A1,MATCH(FALSE,ISNUMBER(0+MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”& LEN(A1))),1)),0)-1)

      這是一個數組公式。

      仍以單元格A1中的數據為“ABC456”,公式3可轉換為:

      =0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“ABC456”,1+6-ROW(INDIRECT(“1:”& 6)),1)),0)-1)

      excel字符串設置

      轉換為:

      =0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“ABC456”,1+6-{1;2;3;4;5;6},1)),0)-1)

      轉換為(注意,這里是如何將得到的數組顛倒,從而使得從字符串的末尾到開頭逐個獲取字符成為可能):

      =0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“ABC456”,{6;5;4;3;2;1},1)),0)-1)

      轉換為:

      =0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+{6;5;4;C;B;A}),0)-1)

      通過適當的數學運算(將數組與0相加),強制數字字符轉換成數字:

      =0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER({6;5;4;#VALUE!;#VALUE!;#VALUE!}),0)-1)

      ISNUMBER函數忽略傳遞給其參數中的錯誤值:

      =0+RIGHT(“ABC456”,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)

      轉換為:

      =0+RIGHT(“ABC456”,4-1)

      其中的4表示在字符串“”中第一個非數字字符自右向左出現的位置,減1表示數字字符出現的位置:

      =0+RIGHT(“ABC456”,3)

      結果為:

      456

      下面,我們來看看字符串中除末尾以外的數字會不會影響最終的結果。例如如果單元格A1中的數據是“123ABC456”,那么公式3可以轉換為:

      =0+RIGHT(“123ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“123ABC456”,1+LEN(“123ABC456”)-ROW(INDIRECT(“1:”& LEN(“123ABC456”))),1)),0)-1)

      轉換為:

      =0+RIGHT(“123ABC456”,MATCH(FALSE,ISNUMBER(0+{“6”;”5”;”4”;”C”;”B”;”A”;”3”;”2”;”1”}),0)-1)

      轉換為:

      =0+RIGHT(“123ABC456”,MATCH(FALSE,ISNUMBER(6;5;4;#VALUE!; #VALUE!; #VALUE!;3;2;1}),0)-1)

      轉換為:

      =0+RIGHT(“123ABC456”,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE},0)-1)

      轉換為:

      =0+RIGHT(“ABC456”,4-1)

      其中的4表示在字符串“”中第一個非數字字符自右向左出現的位置,減1表示數字字符出現的位置:

      =0+RIGHT(“ABC456”,3)

      結果為:

      456

      可以看到,由于是從右向左取數,因此在字符串的其它位置存在數字并不會影響結果。

      RIGHT、MATCH與ISERR

      公式4:

      =0+RIGHT(A1,MATCH(1,0+ISERR(0+MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”& LEN(A1))),1)),0)-1)

      這是一個數組公式。

      以單元格A1中的數據為“123ABC456”,公式4可轉換為:

      =0+RIGHT(“123ABC456”,MATCH(1,0+ISERR(0+{“6″;”5″;”4″;”C”;”B”;”A”;”3″;”2″;”1″}),0)-1)

      轉換為:

      =0+RIGHT(“123ABC456”,MATCH(1,0+{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)

      轉換為:

      =0+RIGHT(“123ABC456”,MATCH(1,{0;0;0;1;1;1;0;0;0},0)-1)

      轉換為:

      =0+RIGHT(“123ABC456”,4-1)

      轉換為:

      =0+RIGHT(“123ABC456”,3)

      結果為:

      456

      與公式3一樣,字符串中除末尾有數字外其它部位存在數字時不影響結果。

      版權聲明:本文內容由網絡用戶投稿,版權歸原作者所有,本站不擁有其著作權,亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容,請聯系我們jiasou666@gmail.com 處理,核實后本網站將在24小時內刪除侵權內容。

      版權聲明:本文內容由網絡用戶投稿,版權歸原作者所有,本站不擁有其著作權,亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容,請聯系我們jiasou666@gmail.com 處理,核實后本網站將在24小時內刪除侵權內容。

      上一篇:excel計算人數的函數是什么(excel函數總人數怎么算)
      下一篇:如何顯示變大?。娔X顯示變大怎么調小)
      相關文章
      亚洲人成黄网在线观看| 亚洲视屏在线观看| 亚洲熟妇久久精品| 色在线亚洲视频www| 亚洲国色天香视频| 亚洲影视一区二区| 亚洲欧洲国产综合| 亚洲老熟女@TubeumTV| 亚洲精品熟女国产| 亚洲伦理一二三四| 亚洲最大福利视频| 亚洲最大中文字幕无码网站| 国产亚洲精品VA片在线播放| 亚洲色少妇熟女11p| 亚洲AV噜噜一区二区三区| 久久亚洲中文无码咪咪爱| 狼人大香伊蕉国产WWW亚洲| 日韩亚洲国产二区| 亚洲精品视频在线观看你懂的| 亚洲精品无码日韩国产不卡?V | 亚洲一级大黄大色毛片| 亚洲欧洲日产国码二区首页| 亚洲自国产拍揄拍| 亚洲色大成网站www| 理论亚洲区美一区二区三区| 亚洲乱码日产精品a级毛片久久| 亚洲综合伊人久久大杳蕉| 亚洲AV无码一区二区二三区软件 | 久久综合日韩亚洲精品色| 亚洲国产精久久久久久久| 91亚洲国产成人久久精品网站| 亚洲小说区图片区| 亚洲欧美日韩中文字幕一区二区三区| 亚洲av永久无码精品秋霞电影秋| 爱情岛论坛亚洲品质自拍视频网站| 亚洲高清成人一区二区三区| 亚洲真人无码永久在线| 亚洲人成影院在线| 亚洲91精品麻豆国产系列在线| 亚洲AV无码之国产精品| 美腿丝袜亚洲综合|