如何從 Excel 儲存格擷取文字
要從儲存格擷取文字,開頭的字元請使用 LEFT,結尾的字元請使用 RIGHT,中間的部分則使用 MID。每個函數都需要文字和字元數。當你要的部分緊接在破折號或空格等字元旁邊時,TEXTAFTER 與 TEXTBEFORE 可以為你找到它。
以下範例拆解一個發票代碼。你可以在結尾的文字課程中練習相同的操作。
1. 使用 LEFT 取得開頭字元
=LEFT(text, count) 會從開頭傳回指定數量的字元。若省略字元數,則只會得到一個字元。RIGHT 的運作方式相同,但從文字結尾開始。
範例:代碼的前綴
| A | B | |
|---|---|---|
| 1 | 代碼 | 前綴 |
| 2 | INV-2024-0183 | INV |
INV-2024-0183 的前三個字元是 INV。
2. 使用 MID 取得中間部分
=MID(text, start, count) 會從任何位置傳回字元。第一個字元的位置是 1。請把破折號也算進去:在 INV-2024-0183 中,I、N、V 和破折號佔據位置 1 到 4,所以年份從位置 5 開始。
範例:中間的年份
| A | B | |
|---|---|---|
| 1 | 代碼 | 年份 |
| 2 | INV-2024-0183 | 2024 |
從位置 5 開始取四個字元得到 2024。結果是文字,因此請使用 VALUE 將其轉為數字。
3. 使用 TEXTAFTER 取得分隔符號之後的文字
當各部分長度不同時,計算字元數就無法運作。TEXTAFTER 會傳回分隔符號之後的文字,其選用的第三個參數可指定使用第幾次出現的分隔符號。它適用於 Microsoft 365 和 Excel 2024。在較舊的版本中,請結合 FIND 與 LEFT、MID 和 LEN 來定位分隔符號。
範例:第二個破折號之後的數字
| A | B | |
|---|---|---|
| 1 | 代碼 | 編號 |
| 2 | INV-2024-0183 | 0183 |
2 表示第二個破折號之後的內容,因此得到 0183,並保留開頭的零。
4. 兩個字元之間的文字
要取得兩個分隔符號之間的內容,請巢狀使用函數:TEXTBEFORE(TEXTAFTER(A2,"-"),"-") 會傳回 2024,即第一個破折號之後、下一個破折號之前的文字。若沒有 TEXTAFTER,請使用 MID 搭配 FIND 計算起點和長度。
5. 將結果轉為數字
LEFT、MID、RIGHT 和 TEXTAFTER 一律傳回文字,即使字元是數字。開頭的零會以文字形式保留,這通常是代碼所需要的。需要計算時,請用 VALUE 包住公式,或將其乘以 1。
常見錯誤
從 0 開始計算起點。 第一個字元的位置是 1。
修正: 從 1 開始計算破折號和其他每個字元。
對長度不一的文字使用固定字元數。 只要有一個名字有四個字母,LEFT(A2,3) 就會出錯。
修正: 用 FIND 找出分隔符號,或使用 TEXTBEFORE 與 TEXTAFTER。
擷取出的數字無法加總。 它們是文字,而 SUM 會忽略文字。
修正: 用 VALUE 包住擷取公式,或將其乘以 1。
常見問題
如何擷取儲存格的第一個字?
使用 =TEXTBEFORE(A2," "),在較舊的版本中則使用 =LEFT(A2,FIND(" ",A2)-1)。更多內容請參閱關於拆分文字的指南。
如何擷取最後一個字?
在 Microsoft 365 中,=TEXTAFTER(A2," ",-1) 會傳回最後一個空格之後的文字。
可以不用公式擷取嗎?
可以。「資料剖析」和「快速填入」可以用按鈕完成,但來源變更時它們不會自動更新。
相關函數
- LEFTLEFT 會傳回文字的前幾個字元,RIGHT 則傳回最後幾個字元。包含語法、產品代碼與姓名的範例,以及如何用 FIND 在空格處切開。.
- MIDMID 會從文字中間傳回字元,只要指定起始位置與長度。包含語法、含代碼與電子郵件地址的範例,以及與 FIND 搭配的用法。.
- TEXTAFTERTEXTAFTER 和 TEXTBEFORE 會以分隔符號(例如電子郵件中的 @)切割文字,不需要計算位置。包含語法與範例(Excel 2024 和 Microsoft 365)。.
- FINDFIND 和 SEARCH 會傳回某段文字在另一段文字中的位置。FIND 區分大小寫,SEARCH 則不區分。包含語法、範例,以及與 LEFT 和 MID 搭配的用法。.
- LENLEN 會傳回文字的字元數,包括空格。包含語法、範例、找出多餘空格,以及計算某個字元出現的次數。.
更多關於 擷取文字
本指南以一個發票代碼示範 LEFT、MID 與 TEXTAFTER。這篇補充的是:如何找出字元的位置、如何以 FIND 擷取長度不一的文字,以及如何以每個部分一個公式,把代碼拆成獨立的欄位。
先找出位置
當各部分長度不一時,就必須計算位置。FIND 會傳回一段文字在另一段文字中的位置,因此破折號的位置就能告訴你在哪裡切開。FIND 區分大小寫,找不到文字時會傳回 #VALUE!;SEARCH 則不區分大小寫,並允許使用萬用字元。
=FIND("-",A2)
=LEFT(A2,FIND("-",A2)-1)長度不一的文字
若要取出第一個破折號之後的部分,請將 MID 與 FIND 及 LEN 結合。從破折號之後的一個字元開始,並要求比剩餘字元數更多的字元,因為 MID 會在文字結尾處停止。使用像 100 這樣很大的數字是常見的技巧。
=MID(A2,FIND("-",A2)+1,100)
=RIGHT(A2,LEN(A2)-FIND("-",A2))每個部分一個公式
若要把 INV-2024-0183 拆成三欄,請為每個部分指定自己的公式:前綴用 LEFT、年份用 MID、號碼用 RIGHT。因為這裡各部分的長度總是相同,固定字數就可以,而且比找破折號的版本更容易閱讀。也要檢查資料底部的幾列,因為最長與最短的代碼,正是固定字數會出錯的地方。
如果結果必須是數字,請將它轉換。至於完整的工具,請閱讀[如何拆分文字](/guides/split-text-in-excel),並在[文字擷取課程](/learn/text-extract)與[擷取單字課程](/learn/extract-words)中練習。