LEFT, MID, and RIGHT in Excel and Google Sheets

I 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-103

No #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)   → 103

Microsoft’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)) → FALSE

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