用 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.