用 LEFT、MID、RIGHT 按固定位置擷取文本
在 Google 表格裡對 SKU-103 測試：LEFT 取前 3 個字元得到 SKU，MID 從第 5 位取 3 個得到 103，RIGHT 取後 3 個也是 103。連字元在第 4 位。微軟文件把第一個字元計為第 1 位。位置會變動時，不要用這組函式，改用查詢或拆分。
本文目錄
快速解答
在 Google 表格裡對 SKU-103 測試：LEFT 取前 3 個字元得到 SKU，MID 從第 5 位取 3 個得到 103，RIGHT 取後 3 個也是 103。連字元在第 4 位。微軟文件把第一個字元計為第 1 位。位置會變動時，不要用這組函式，改用查詢或拆分。
=LEFT(A2,3) → SKU
=MID(A2,5,3) → 103
=RIGHT(A2,3) → 103
=MID(A2,4,1) → -檢查重點
問得比單元格還長時，這裡不會報錯。ab 用 LEFT 或 RIGHT 取 5 個字元，結果仍是 ab。SKU-103 用 RIGHT 取 10 個字元，得到整串。MID 從第 5 位取 20 個字元，只返回到末尾的 103，不會補空格。這和微軟對 LEFT、RIGHT、MID 的說明一致。LEFT 取 0 個字元、以及對空單元格取 LEFT，在本表裡都是空白。
邊界與相容性
A4 是文本 001，LEFT 取 2 個字元得到 00，ISTEXT 為 TRUE，前導零還在。A6 是數字 12345，LEFT 取 2 個字元得到 12，看起來像數字，但 ISTEXT 為 TRUE、ISNUMBER 為 FALSE。不要假定擷取數字後仍是數值。微軟當前的 LEFT 頁面沒有寫清這種型別轉換，所以這裡只記錄 Google 表格的結果。
完整英文範例、公式與作者備註
Excel How-toI keep reaching for SPLIT when the thing I want is already sitting in a fixed place. SKU-103 is seven characters. The prefix is the first three, the hyphen is character 4, and the item number is the last three. No delimiter hunt. I put that value in A2 and ran the three functions that have been in every spreadsheet I have used since the 2000s.
=LEFT(A2,3) → SKU
=MID(A2,5,3) → 103
=RIGHT(A2,3) → 103
=MID(A2,4,1) → -MID is the one people trip on: the second argument is a start position, not “skip this many.” I wanted the 103 after the hyphen, so I started at 5. Starting at 4 would have included the hyphen. Microsoft’s MID function page numbers the first character as 1, which is what Sheets did here.
These three are a bad fit once the hyphen moves around (SKU-9 versus SKU-1001). Then you want FIND plus MID, or a real split. I wrote those up in Split Text by Delimiter and Extract Text Between Characters. This page is only the fixed-width case.
Asking for more characters than the cell has
A3 was just ab. I asked for five characters on purpose.
=LEFT(A3,5) → ab
=RIGHT(A3,5) → ab
=RIGHT(A2,10) → SKU-103No #VALUE!. Sheets handed back the whole string. That matches what Microsoft writes for LEFT and RIGHT: if num_chars is longer than the text, you get all of the text.
MID is a little different when the start is still inside the string but the length runs off the end. A2 is seven characters, and position 5 is the 1 of 103:
=MID(A2,5,20) → 103Microsoft’s MID page says the same: you get the characters through the end, and it does not pad spaces. I did not test a start position past the last character, or a negative length; I am not going to guess those.
LEFT(A2,0) returned a blank cell. So did LEFT of an empty cell. I would not build a check around that; use LEN or ISBLANK if you care whether anything is there.
The result is text, even when it looks like a number
A4 was the text 001 (typed as text, not the number 1). A6 was the real number 12345.
=LEFT(A4,2) → 00
=ISTEXT(LEFT(A4,2)) → TRUE
=LEFT(A6,2) → 12
=ISTEXT(LEFT(A6,2)) → TRUE
=ISNUMBER(LEFT(A6,2)) → FALSETwo useful facts. First, LEFT on the text 001 keeps the zeros, which is what you want on a SKU or a ZIP; Keep Leading Zeros is the longer version of that fight. Second, LEFT of a true number still came back as text. 12 looks numeric. ISNUMBER said it was not. I did not then add that value to anything; I am only reporting the type.
Microsoft’s current LEFT page does not spell out the number-to-text conversion in the remarks I read, so I am not going to claim how desktop Excel types the same formula. In this Sheets file, do not assume LEFT of a number is still a number.
If you are cutting first and last names, positions are usually the wrong tool anyway. Use Split a Full Name.