學不完．教不停．用不盡

網友問到 Excel 的問題：如下圖，如何把原始資料中的每個數字除以 100？

這個例子的原始資料中有三個數字，並且利用 2 個『*』加以隔開。

【公式設計與解析】

本例將不使用 FIND 函數和 SEARCH 函數來找尋『*』的位置，再取出三組數字。這次將要用特殊的做法，巧妙來取出三組數字。

儲存格C2：

=LEFT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)/100&"*"&MID(SUBSTITUTE(A2,
"*",REPT(" ",20)),15,15)/100&"*"&RIGHT(SUBSTITUTE(A2,"*",REPT(" ",20)),
6)/100

(1)轉換儲存格內容：SUBSTITUTE(A2,"*",REPT(" ",20))

先利用 REPT 函數產生 20 個空格(" ")，再利用 SUBSTITUTE 函數將『*』置換成 20 個空格。

(2) 計算第一組數字：LEFT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)/100

LEFT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)：利用 LEFT 函數取出轉換後的儲存格內容最左邊 6 個字元(假設數字加小數點不超過 6 個數)。當這個 6 個字元的字串除以 100 時，Excel 會自動將串轉換為數值(空格會自動消除)。

(3) 計算第二組數字：MID(SUBSTITUTE(A2,"*",REPT(" ",20)),15,15)/100

MID(SUBSTITUTE(A2,"*",REPT(" ",20)),15,15)：利用 MID 函數由第 15 個字取 15 個字元(取出的 15 個字元中必定包含第二組數字)。再藉由將此結果除以 100，得到其中的數字。

(4) 計算第三組數字：RIGHT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)/100

RIGHT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)：利用 RIGHT 函數取出轉換後的儲存格內容最右邊 6 個字元(假設數字加小數點不超過 6 個數)。再藉由將此結果除以 100，得到其中的數字。

(5) 組合字串：第(2)式&"*"&第(3)式&"*"&第(4)式