下圖左邊的表格是由教育部網站取得:
https://language.moe.gov.tw/001/Upload/files/ ... /download/86rest17.TXT
在以往的表格框線是由全型字元所拼成,如果複製要 Excel 的工作表中,將會出現像圖中的不規則樣。所以想要寫一個公式將它轉換成正確的表格。
【公式設計原理】
儲存格C3:=SUBSTITUTE(MID(SUBSTITUTE($A13,"│",REPT(" ",50)),(COLUMN(B1)-1)*50+1,60)," ","")
1. SUBSTITUTE($A13,"│",REPT(" ",50))
目的:將儲存格 A13 中的所有「│」字元,替換成 50 個空白(" ")。
做法:REPT(" ",50) 會產生 50 個連續的空白字元。SUBSTITUTE 用這 50 個空白字元取代原本字串中的「│」。
結果:如果 A13 原本像是 文字1│文字2│文字3,經過這個替換,會變成 文字1 文字2 文字3(每個「│」位置現在有 50 個空白)。
這一步的重點在於:利用 50 格空白來模擬一個「足夠寬」的分隔符號,讓後續的 MID 函數能藉由「切一大段空白」去定位各段文字。
2. (COLUMN(B1)-1)*50+1
目的:決定要從哪個字元位置開始擷取。
做法:COLUMN(B1) 的結果是 2(因為 B 欄是第 2 欄)。
在 C 欄時,COLUMN(B1) 還是參考 B1 的欄位編號 2,不會因為在 C 欄而變成 3。
公式中的 (COLUMN(B1)-1) 就是 (2 - 1) = 1。
進一步乘以 50:1 * 50 = 50。
最後再加 1:得到 50 + 1 = 51。
結果:當公式位於 C13 時,(COLUMN(B1)-1)*50+1 會是 51,也就是說 MID 函數要從第 51 個字元開始擷取。
為何要用 (COLUMN(B1)-1)*50+1?
這個寫法通常配合「向右複製」或「向下複製」時,會依照欄位或列數動態變化,以擷取分段文字。若後續同樣原理用在 D 欄、E 欄時,可能會改用 COLUMN(C1)、COLUMN(D1) 等方式,讓每一欄都取不同區段的文字。
3. MID( ... , 51, 60)
目的:在第 2 步求出的位置(例如 51)開始,取 60 個字元。
做法:MID(文字串, start_num, num_chars) 會從 start_num 開始,擷取 num_chars(這裡是 60)個字元。
結果:因為在第 1 步時,每個「│」被替換成 50 格空白,所以每個「區段」約莫用 50 格空白來分隔。再多給一些(這裡用 60)只是確保可以包含完整文字以及空白,讓之後可以去掉多餘空白。
4. SUBSTITUTE( ... ," ","")
目的:將第 3 步得到的那一段文字裡,多餘的空白字元全部拿掉。
做法:SUBSTITUTE(前面MID結果, " ", "") 會把所有空白 (" ") 直接替換成空字串("")。
結果:原先因為「│」被替換成 50 格空白,MID 擷取到的區段除了原本的文字之外,還會包含一堆空格。這一步就把那些空格移除,只留下該段真正的內容。
5. 結合整體理解
將「│」替換成 50 個空白:把原始字串分割成一大段一大段的形式,方便用 MID 切分。
計算擷取起點 (COLUMN(B1)-1)*50+1:隨著欄位(或列)不同,動態切出某一段所需的文字。
從替換後字串擷取 60 個字元:確保取到那一段的所有文字(包含可能多的空白)。
再把所有空白移除:得到最終的純文字。
所以,如果原本 A13 存了好幾段以「│」分隔的文字,當你把這個公式從 C 欄一路複製到其他欄位(例如 D、E、F... 等),每一欄都會因 (COLUMN(B1)-1)*50+1 計算出不同的起始位置,對應分別取得不同段落的文字,最後再把空白去掉,達到「切分文字」的效果。
結論:
這個公式的整體邏輯是「把分隔符號改成大段空白,然後用 MID 依照不同欄位去擷取相對應的分段,最後再把多餘空白刪除」。如此便能依據「│」拆解 A13 的內容,並在各欄得到對應的文字。
如果你有從頭理解公式,不知看懂了嗎?其實這是我將設計好的檔案和公式上傳給 ChatGPT,請 AI 為我寫公式的運作原理,順便要測試 ChatGPT 的能力。其實,說明的結果有合我意。
所以讀者們如果取得他人給的 Excel 檔,工作表中有公式看不懂的話,也可以上傳讓 ChatGPT 直接當老師教你哦!
https://chatgpt.com/share/67d554e8-47c8-8008-9030-ec1a2e988d8c