文字清理函式參考：TRIM、CLEAN、SUBSTITUTE、PROPER 與看不見的空格

從別的系統匯出來的名單，兩格看起來一模一樣，=A2=A5 卻回 FALSE，XLOOKUP 也找不到。八成是某一格多了你看不見的字元。

今天把整理文字的函式排成一張參考表：TRIM、CLEAN、SUBSTITUTE、REPLACE、UPPER、LOWER、PROPER，再加上兩個抓兇手用的 UNICODE 和 CHAR。把數字或日期變成指定樣子的 TEXT 在 TEXT 函式入門 講過了，這篇不重複。

先抓出兇手

我用四格「蘋果紅茶」來示範，每一格混進不同的空白。這份資料我是用公式造的，因為看不見的字元用貼的很容易被吃掉：

四格看起來幾乎一樣。先用 LEN 數字元數，乾淨的 A5 是 4 個字：

只有 A2 被 TRIM 清乾淨，A3 和 A4 跑完 TRIM 字元數沒變。為什麼？下面一個一個函式看，最後再回來把這四格全部清成 4。

怎麼選

空白與看不見的字元

TRIM

清掉前後的空格，並把中間連續的空格壓成一個。

語法：=TRIM(文字)

• 文字：要整理的文字或儲存格。

結果是 apple milk tea。

TRIM 處理的是一般半形空格（字元碼 32）。例如 LEN(TRIM(" x ")) 是 1；LEN(TRIM(CHAR(160)&"x"&CHAR(160))) 與 LEN(TRIM("　x　")) 都仍是 3。全形空格與不換行空格要先辨識、替換，不能只靠 TRIM。

CLEAN

清掉不可列印的控制字元，像是換行、Tab。

語法：=CLEAN(文字)

• 文字：要整理的文字或儲存格。

CLEAN 清的是 ASCII 碼 0 到 31 的字元。換行是 CHAR(10)、Tab 是 CHAR(9)，都在這個範圍裡：

結果是 appletea，兩個字黏在一起。因為它只會拿掉，不會補空格，所以有換行分隔單字的資料，我會先用 SUBSTITUTE 把 CHAR(10) 換成空格，再交給 TRIM，不會直接丟給 CLEAN。

CLEAN 不是 Unicode 正規化。CLEAN("Ａ１") 與 TRIM("Ａ１") 都保留全形 Ａ１，ASC("Ａ１") 才得到 A1。是否要轉全半形應由資料規則決定；ASC 也不等於完整的 Unicode NFKC 正規化。

CHAR(160)、全形空格與零寬空格（UNICHAR(8203)）不屬於前述 ASCII 控制字元，不應假設 CLEAN 能全部清理。需要保留分隔時先替換，別讓文字直接黏起來。

SUBSTITUTE

把文字裡的某一段換成別的。這是我清資料最常用的函式，換成空字串就是刪除。

語法：=SUBSTITUTE(原文字, 搜尋文字, 取代文字, [第幾次])

• 原文字：要處理的文字或儲存格。
• 搜尋文字：要找的字，大小寫要完全一致。
• 取代文字：換成什麼，填 "" 就是刪掉。
• 第幾次：選填。不填就是全部換掉，填 2 就只換第 2 次出現的那個。

先看最常見的，把千分位逗號拿掉：

結果是 1286000，不過它是文字，不是數字。要拿去加總得再用 VALUE 轉，或乘以 1，這件事在 值與格式的分離 有講。

第 4 個參數只換第 N 次：

結果是 2026-10/11，第 1 個減號沒動。如果第 4 個參數寫 3，這個字串只有兩個減號，整串會原封不動。不填第 4 個參數的話，兩個都換，會得到 2026/10/11。

SUBSTITUTE 分大小寫：

結果是 Apple X。

還有一招，數某個字出現幾次：原本的長度，減掉刪光那個字之後的長度。"1,286,000" 有 9 個字元，刪掉逗號剩 7 個，所以有 2 個逗號：

用函式算，就是 2。

REPLACE

照位置換，不看內容。

語法：=REPLACE(文字, 起始位置, 長度, 新文字)

• 文字：原本的文字。
• 起始位置：從第幾個字元開始換。
• 長度：換掉幾個字元，填 0 就是在那個位置插入。
• 新文字：要放進去的內容。

最適合遮電話號碼中間三碼：

結果是 0912***678。在第 5 個位置插入斜線，長度填 0：=REPLACE("20261011", 5, 0, "/") 得到 2026/1011。

坦白說，我用 SUBSTITUTE 的機會比 REPLACE 多很多。知道「換什麼字」比數「第幾個字」穩，資料前面多一個字，位置就全部偏掉了。

字母大小寫

這三個函式處理有大小寫的字母，不只適用於英文。數字與中文通常保留，但字母邊界、附加符號與 Unicode 表示方式會影響結果，不能當成姓名標準化工具。

UPPER

全部轉大寫。

語法：=UPPER(文字)

結果是 AB-1203X。中英混排時中文會保留，=UPPER("蘋果tea") 得到 蘋果TEA。

LOWER

全部轉小寫。

語法：=LOWER(文字)

信箱、帳號比對前，我習慣先全部轉小寫：

結果是 shop@example.com。

PROPER

每個英文單字的第一個字母大寫，其他字母轉小寫。

語法：=PROPER(文字)

結果是 Milk Tea。

中英混排、數字與非 ASCII 字母都要看實際結果，下面是 Google 試算表的輸出：

最後兩列看起來相近，Unicode 表示卻不同。官方說明也提醒 PROPER 會改小非詞首字母，例如姓名可能因此失真。客戶姓名、品牌、型號應保留原始欄、另做候選清理欄並人工確認，不要整欄直接覆寫。多語情境的更多討論可參考方格子的大小寫舊文。

把多行清單改成頓號

例如一格內的 a、b 用換行分隔，想讓它顯示成 a、b，要保留分隔就用 SUBSTITUTE，別直接 CLEAN：

實際使用時可把第一個引數換成存放清單的儲存格。若來源有 Windows 的 CRLF，還要分辨 CHAR(13) 與 CHAR(10)，不要假設所有檔案的換行都相同。這個短案例改寫自方格子 SUBSTITUTE 舊文。

只改首字元，不改其餘大小寫

PROPER 會改動後面的字母。如果只想改第一個字元，可以用 REPLACE 按位置換，其他字元維持原樣：

結果是 HELLO，不是 Hello：後四個字元本來就是大寫，公式沒有幫你轉成小寫。REPLACE 依位置操作，不是搜尋關鍵字，沒有「只找第一次符合文字」的規則。

有「01.青茶」這種序號時，可先找點號的位置，再取後面的文字；沒有點號時則保留原值：

結果是「青茶」；把三處輸入都改成「青茶」（沒有點號）時仍是「青茶」。用 REPLACE 刪除開頭到點號的字元也可以，有點號與無點號兩種輸入都能處理：

這個版本移除第一個點號之前的所有內容，不會驗證前面一定是數字，資料含小數或名稱本來有點號時要先確認規則。相關構想改寫自方格子 REPLACE 舊文。

抓出怪字元

UNICODE

回傳文字第一個字元的 Unicode 編碼。看不見的字元不知道是誰的時候，用它來問。

語法：=UNICODE(文字)

• 文字：只看第一個字元。

要看 A4 最後那個字元，用 RIGHT 取出來再問：

結果是 160，就是不換行空格。同樣的方法問 A3 的第一個字元，會是 12288，全形空格。半形空格是 32。

CHAR

反過來，給編碼回傳那個字元。

語法：=CHAR(編碼)

• 編碼：字元的十進位編碼。CHAR(10) 是換行，CHAR(160) 是不換行空格。

CHAR(160) 我主要用在清理的時候，告訴 SUBSTITUTE 要找的是哪一個字元，因為這個字很難直接打進公式裡。另外還有 UNICHAR，用法一樣，給編碼回傳字元。

把四格一次清乾淨

回到開頭的四格。全形空格和不換行空格先換成半形空格，剩下的交給 TRIM：

第一個 SUBSTITUTE 裡引號中的那個字是全形空格，很容易打成半形，打完可以用 LEN 檢查。四格往下複製，LEN 全部是 4，和乾淨的 A5 也都相等了。

要連換行一起處理的話，再加一層 CHAR(10)，最後用 CLEAN 收掉其他控制字元：

我拿一格很亂的英文來試，A6 是 =" apple"&CHAR(10)&"MILK"&CHAR(160)&"tea "，共 17 個字元，開頭有空格、中間有換行跟不換行空格、結尾兩個空格。清完是 Apple Milk Tea，14 個字元。

整欄都要清的話，可以搭配 ARRAYFORMULA，寫法在 ARRAYFORMULA 自動延伸 有說明。清完的欄位我通常會貼成值，再把原始欄隱藏起來。

清好之後，UNIQUE、XLOOKUP 這類函式才不會被假性不相等騙到。可以接著看 XLOOKUP 進階。

我是喜特先生，Mr. Sheet，我們下個教學見！