下圖左邊的表格是由教育部網站取得：
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
留言列表