LEFT、MID、RIGHT で固定位置の文字を取り出す
Google スプレッドシートで SKU-103 を試した。LEFT で先頭 3 文字は SKU、MID で 5 文字目から 3 文字は 103、RIGHT で末尾 3 文字も 103。ハイフンは 4 文字目。Microsoft の説明でも先頭は位置 1。位置が変わるなら FIND や分割を使う。
目次
要点
Google スプレッドシートで SKU-103 を試した。LEFT で先頭 3 文字は SKU、MID で 5 文字目から 3 文字は 103、RIGHT で末尾 3 文字も 103。ハイフンは 4 文字目。Microsoft の説明でも先頭は位置 1。位置が変わるなら FIND や分割を使う。
=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 だけで、空白は埋まらない。Microsoft の LEFT、RIGHT、MID の説明と同じ。LEFT の文字数 0、および空セルの LEFT は、ここでは空白だった。
注意点と互換性
A4 は文字列の 001。LEFT で 2 文字取ると 00 で、ISTEXT は TRUE、先頭のゼロは残る。A6 は数値の 12345。LEFT で 2 文字取ると 12 に見えるが、ISTEXT は TRUE、ISNUMBER は FALSE。切り出した結果を数値だと思わないこと。Microsoft の現行 LEFT ページはこの型変換を明記していないので、ここではスプレッドシートの結果だけを書く。
英語の詳細例・数式・著者メモ
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.