點樣set呢條excel formula...

Excel使用上，常可能得到的資料是從外部匯入，所以會有文字與數字在同一格儲存格的問題，若之間有分隔符號，那麼可以用「資料剖析」來完成分離，若是固定位數的字串，可以用Right、Left、Mid這些函數來達到分離，但若是數字與文字都不固定位數的字串，要怎麼分離呢？接下來就說明是怎麼去完成函數的。

原始範例如下：
1. 思考點是在於要怎麼知道數字與文字的分隔點在第幾位，所以就要用 ISNUMBER 來判別，那要怎麼把各個位數的字挑出來呢？就想到要用 MID 這函數，因為它可以自行設定要抓取的字元。這兩個思考點，就完成了這段程式： B2 =ISNUMBER(MID(A2,1,1))

2. 而 B2 的結果是：FALSE ，因為 A2 內容的第 1 的字是"中"所以是 FALSE 。接下來的思考，就是希望能抓取的是每個字，而不是只有第一個字，所以公式要改成： B2 =ISNUMBER(MID(A2,ROW(A1:A6),1))
這邊大概說明 Row(A1:A6) ， Row 是抓取列數的函數，所以這樣就等於1~6，所以上面公式也可以改為： B2 =ISNUMBER(MID(A2,{1,2,3,4,5,6},1))
在輸入完後，可以嘗試把公式中 MID(A2,ROW(A1:A6),1)) 反白後按F9，就可以預覽公式計算過程（Esc鍵可取消預覽），結果為：B2 =ISNUMBER({"王","小","方","1","2","3"})

3. 前面兩步驟已經把字串中字元個別抓出，接著就用 ISNUMBER 來判別是不是數字，所以可以再嘗試 B2 全部公式反白後按F9，這樣看到的結果是：B2 ={FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}
但是依照 A2 的字串，應該在第 4 位就是數字，怎麼上面 6 位的判定都是 FALSE ？原因就在第 2 步的最後那行公式：B2 =ISNUMBER({"王","小","方","1","2","3"}) ，可以看到 1、2、3 都被 " " 所框選，這樣它就是字串，非數字。要避免這問題，那麼就要強制把字串轉換成數字，就把公式改為： B2=ISNUMBER(--MID(A2,ROW(A1:A6),1)) ，預覽結果為： B2={FALSE;FALSE;FALSE;TRUE;TRUE;TRUE} ，這樣就達到我們需要的判別功能了！

4. 以上就達到判定字串中各字元的屬性功能，但是儲存格顯示的依然是 FALSE ，因為上面所運用的都是 陣列 的觀念，但單一儲存格一般無法顯示陣列的結果，所以我們就透過函數 SUMPRODUCT 來統計 TRUE 的數目，那麼 B2=SUMPRODUCT(ISNUMBER(--MID(A2,ROW(A1:A6),1)))
但是明明有三個數字（TRUE），為何加總結果還是 0 ？原因很簡單，就跟第 3 步驟一樣問題， ISNUMBER 函數判別結果的表是方式是 TRUE、FALSE ，所以這是邏輯值不能做加總的，那麼就一樣把它強制轉換成數字吧，公式： B2=SUMPRODUCT(--ISNUMBER(--MID(A2,ROW(A1:A6),1))) ，這樣計算結果就是 3 個數字。

5. 前面四步驟就可以把數字的個數統計出來，但有一點需要注意的就是 ROW(A1:A6) ，這是代表要抓取 6 位字元來做判斷，但範例中有大於小於 6 位的字串，那麼我們就把公式改為讓它可以自動判定字串長度（函數LEN），所以這邊公式就改成： ROW(INDIRECT("A1:A"&LEN(A2)))，以上達到的效果就是下圖：（B欄為A欄字串中數字的個數）



6. 到這一步只要搭配 RIGHT、MID、LEN 就可以達到擷取文字、數字字串的能力了，相反的若數字在左，文字在右的排列，就是用 LEFT、MID、LEN 來擷取！

字串擷取就用 MID、LEN ，所以就等於 =MID( 字串 , 從左邊第1個字開始 , 抓字串長度-數字位數 )

B2=MID(A2,1,LEN(A2)-SUMPRODUCT(--ISNUMBER(--MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1))))

而數字部分用RIGHT，所以等於 =RIGHT( 字串 , 數字位數 )

C2=RIGHT(A2,SUMPRODUCT(--ISNUMBER(--MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1))))

我想SET OVERTIME TIME STAR
-MON -18:00 ONE HRS HKD40 TO SAT 2:30 PM ONE HOUR HKD50-

你首先將它們剪下, 貼上記事本(附屬應用程式)上, 用"Tab"分開它們, 再剪下貼回excel就可以了.