1樓:陸離Lowry
=TRIM(MID(SUBSTITUTE($A1,"*",REPT(" ",99)),1+(COLUMN(A1)-1)*99,99)),向右拖拽複製公式
為什麼非要用公式?ctrl+e不香?分列不香?power query不香?
2樓:小灰灰
乙個公式的通解。
=TRIM(LET(y,SUBSTITUTE(A1,"*",REPT(" ",10)&"*"),x,IF(MID("*"&y ,ROW($1:$999),1)="*",ROW($1:$999),999),MID(y,SMALL(x,COLUMN(A:
C)),10)))
LET函式需要EXCEL 2019才可以使用。
先說下原理,高手可以略過
通過substitute替換*號為10個空格加* ,為最後取數做準備;
然後通過IF函式,返回序列中*號出現的位置,注意我們在前面加了乙個*號,表示要從第乙個位置取。
再用Small函式獲取出現的3次位置
通過MID函式次從3個位置取10個長度。因為第2步查詢時,我們在前面多加了乙個*,所以可以直接用位置。
最後用Trim函式把多加的空格清空。
用下面公式也可以,更快捷一點。
=LET(x,IF(MID("*"&A1,ROW($1:$999),1)="*",ROW($1:$999),999),MID(A1,SMALL(x,),SMALL(x,)-SMALL(x,)-1))
關鍵在於MID取數時,需要通過兩個陣列相減,再減一得到取數的長度。
再寫乙個吧,有的同學不能用LET函式。
=LEFT(SUBSTITUTE("^^"&$A1,"^^"&TEXTJOIN("*",1,$B1:B1)&IF(OR(LEN(B1)=0,$A1=TEXTJOIN("*",1,$B1:B1)),"","*"),""),IFERROR(FIND("*",SUBSTITUTE($A1,TEXTJOIN("*",1,$B1:
B1)&IF(LEN(B1),"*",""),"")),99)-1)
注意要把B1空出來,從C1開始寫公式,然後右拉。
大致思路如下:
然後再查詢*號的位置,找不到則用99代零星;
從左取到*號位置減一的資料。
注意有兩個重點:
乙個是替換時前面加上兩個^^ 符號,如果第乙個是36,但是公式裡有336,則可以一樣會替換掉;
第二個是TEXTJOIN後面加*號,只有當有資料且資料鏈結後不等於字串時才加。
當然,這個公式也可以改成LET版。
=LET(x,TEXTJOIN("*",1,$B1:B1),LEFT(SUBSTITUTE("^^"&$A1,"^^"&x&IF(OR(LEN(B1)=0,$A1=x),"","*"),""),IFERROR(FIND("*",SUBSTITUTE($A1,x&IF(LEN(B1),"*",""),"")),99)-1))
3樓:王備
有個特別特別特別簡單的方法,裝乙個方方格仔的外掛程式Excel工具箱外掛程式-方方格仔工作室-做最專業的Excel工具箱然後他有個textsplit函式,可以直接拆。
優點是簡單易用,缺點是這個函式只能在你自己電腦上用,發給別人的時候除非對方也裝了工具,要不就出問題了
4樓:徐徐不倦
看見題主回覆後更新:
是我腦袋卡改錐了,把簡單的問題複雜化了。可以直接用FIND函式的。
1、實現效果
2、示例公式
D2公式
=LEFT(A2,FIND("*",A2)-1)
E2公式
=MID(A2,FIND("*",A2)+1,FIND("*",MID(A2,FIND("*",A2)+1,999))+FIND("*",A2)-FIND("*",A2)-1)
F2公式
=MID(A2,FIND("*",MID(A2,FIND("*",A2)+1,999))+FIND("*",A2)+1,999)
其中A2單元格為待拆分單元格,複製可用。
3、公式原理
B列,用FIND函式查詢第1個「*」的位置號
=FIND("*",A2)
C列,用FIND函式查詢第2個「*」的位置號
=FIND("*",MID(A2,B2+1,999))+B2
D列,取第1個乘數
=LEFT(A2,B2-1)
E列,取第2個乘數
=MID(A2,B2+1,C2-B2-1)
F列,取第3個乘數
=MID(A2,C2+1,999)
最後再把B、C輔助列公式套進公式內就可以了。
4、公式講解
難點可能就是C列,用FIND函式查詢第2個「*」的位置號。
=FIND("*",MID(A2,B2+1,999))+B2
公式中B2為第1個「*」的位置號,公式首先將原字串第1個「*」以及它左邊的部分刪掉,然後在新的字串中再次查詢「*」的位置號,最後再加上B2就可以了。
從例子上說,500*1000*30第1個「*」的位置號是4,那麼這個公式首先將500*1000*30第4個字元以及第4個字元左邊的部分刪掉,變為1000*30,然後再查詢「*」的位置號為5,最後加上第1個「*」的位置號4,得到第2個「*」的位置號為9。
以下為腦袋卡改錐的原答案:
可以用函式巢狀實現。
0-1、實現效果
0-2、示例公式
第乙個數字
=LEFT(A2,MIN(IF(MID(A2,ROW($2:$21),1)="*",ROW($1:$20),999)))
第二個數字
=MID(A2,MIN(IF(MID(A2,ROW($1:$20),1)="*",ROW($2:$21),999)),MAX((MID(A2,ROW($1:
$20),1)="*")*ROW($1:$20))-MIN(IF(MID(A2,ROW($1:$20),1)="*",ROW($2:
$21),999)))
第三個數字
=MID(A2,MAX((MID(A2,ROW($1:$20),1)="*")*ROW($2:$21)),99)
0-3、特別注意
上述三個公式為陣列公式,要三鍵結束輸入(CTRL+SHIFT+ENTER)
Excel能否用純單元格公式隨機生成乙個簡單的加法等式?
hyc11072 用複製列可以解決,但是直接解決不了。隨機數每個都可能不同,所以在公式中不存在兩個完全一樣的隨機數。所以不能假設兩個隨機數相同。 excelfans 我覺得,等號以前的可以生成,生成以後,就不能再繼續啟動隨機函式了,就得對生成的結果,進行計算,這個和就應該是個和前面隨機結果是對應的乙...
excel同乙個單元格有上下兩行數字,怎麼求和
星辰 同乙個單元格有上下兩行數字,是使用換行符強制換行了,這樣換行之後資料型別變成了文字格式,不能和數值型別的資料直接相加,可以用函式TYPE判斷一下,會發現兩者資料型別不一樣,求和會報錯。可以先去除換行符,再進行求和,加乙個輔助列,用函式CLEAN去除資料列的換行符。Excel去換行符麻煩?用函式...
請問如何做到在同乙個單元格裡下拉時日期逐漸遞增,時間不變?
友情提示本文答案在最後 問題本身很簡單,但卻有乙個很大的坑,本來有人已經回答了,可是這樣存在乙個問題 我需要日期時間,而不是 文字 日期時間的本質到底是什麼?自定義格式 hh mm會統計小時數和分鐘數,30小時50分鐘 gt 30 50 上圖可以看出其實日期和時間的本質還是數字,比如 1.5 190...