Excel Text Functions: LEFT, RIGHT, MID Basics

Most tutorials will tell you that LEFT, RIGHT, and MID are simple functions — just point them at a cell and pull characters. That's technically true, and it's also why so many people get burned by them. The real problem with Excel text functions isn't the syntax. It's that nobody explains why Excel counts the way it does, so when things break, users have no idea where to look. I've spent nearly 20 years working with Excel daily (a decade of that doing financial analysis where cleaning messy imported data was a Monday morning ritual), and the people who struggled most with these functions all shared the same gap: they memorized the formula without understanding the logic. By the end of this guide, you'll know when to reach for each function, how to chain them together for real parsing tasks, and where they silently fail.

No advanced Excel knowledge needed. If you can type a formula into a cell, you're ready. We'll use a single product SKU, CAT-042-RED, as our example throughout, so you can see exactly how each function behaves on the same text string.

---

Step 1: Decide Which Excel Text Function to Reach For (LEFT, RIGHT, or MID)

The decision comes down to one question: where in the cell are the characters you need? If you know the answer to that, the function choice is automatic. These three functions are available in every version of Microsoft Excel — no Microsoft 365 subscription or add-in required.

When the characters you need are at the start of a cell, use LEFT

The LEFT function pulls a specified number of characters from the beginning of a text string.

Syntax: LEFT(text, num_chars)

Give it a cell and tell it how many characters to grab, starting from the left.

For CAT-042-RED, to extract the category prefix CAT:
=LEFT(A2, 3) returns CAT

Excel counts from the left because that's the only unambiguous starting point. Once you internalize that, MID's argument structure stops feeling arbitrary.

When the characters you need are at the end, use RIGHT

The RIGHT function works identically, just from the other direction.

Syntax: RIGHT(text, num_chars)

Count inward from the last character.

=RIGHT(A2, 3) on CAT-042-RED returns RED

When the end position isn't fixed (say, color codes of varying lengths), pair RIGHT with the LEN function: =RIGHT(A2, LEN(A2) - FIND("-", A2, 5)). LEN counts the total characters in the string, giving RIGHT a dynamic number to work from.

When the characters are buried in the middle, use MID

The MID function needs three pieces of information: the text string, where to start, and how many characters to return.

Syntax: MID(text, start_num, num_chars)

Start at character n, grab x characters from that point forward.

=MID(A2, 5, 3) on CAT-042-RED returns 042

MID's start position is 1-based, not 0-based. Character 1 is the first character — there is no zero position in Excel. This trips people up every time at first, especially if you've worked in any programming language that uses zero-based indexing.

---

Step 2: Combine Excel Text Functions to Solve a Real Parsing Task

Once you know which function fits which position, the next step is combining them. This is where most beginner guides stop short. Individual syntax examples are fine for reference, but real data rarely has fixed, predictable positions. Names are the classic example.

Chain LEFT and FIND to extract a first name without knowing its length in advance

Say column A contains full names: Sarah Chen, Marcus Rivera, James Okafor. You need the first name only — but each one is a different length, so =LEFT(A2, 5) won't work.

The fix is using FIND to locate the space, then feeding that position into LEFT:

=LEFT(A2, FIND(" ", A2) - 1)

FIND returns the position of the first space. Subtract 1 and you get the exact character count of the first name — no hardcoding required. Applied to Sarah Chen, FIND returns 6, so LEFT gets 5 characters: Sarah.

This is the combination that most Excel text formula tutorials never show, and it's the pattern that actually appears in real data work. For a deeper look at how formulas like this fit together, the Excel Formulas and Functions for Beginners guide covers the building blocks you'd want solid before chaining multiple functions.

---

Step 3: Try TEXTBEFORE and TEXTAFTER If You're on Microsoft 365 (It's Faster)

That LEFT+FIND combination works everywhere — but if you're on Microsoft 365, there's a cleaner path.

TEXTBEFORE and TEXTAFTER arrived in 2021 and most users still haven't found them. They replace the entire nested formula with something readable:

Old way: =LEFT(A2, FIND(" ", A2) - 1)
New way: =TEXTBEFORE(A2, " ")

Same result. Half the characters to type. No mental math on the -1 offset.

=TEXTAFTER(A2, " ") pulls everything after the delimiter, so Sarah Chen becomes Chen. No RIGHT+LEN gymnastics needed.

Stick with the classic functions if you're sharing files with colleagues on older Excel versions, or if your organization hasn't rolled out Microsoft 365. TEXTBEFORE and TEXTAFTER return errors on Office 2019 and earlier.

The difference between formulas and functions in Excel is worth understanding here too — TEXTBEFORE behaves differently than you might expect if you're used to classic string logic.

---

Common Mistakes With Excel Text Functions (Including the Space Problem Nobody Mentions)

Here's the part most tutorials skip.

1. Spaces count as characters. If your source data has a trailing space after CAT-042-RED (common in data exports), =RIGHT(A2, 3) might return ED  instead of RED. You won't see the space. The cell will look fine. But any subsequent VLOOKUP or XLOOKUP will fail silently. Wrap extraction results in TRIM as a default habit, not optional cleanup: =TRIM(LEFT(A2, 3)). Do it before you need it.
2. MID's start position is 1, not 0. =MID(A2, 0, 3) returns an error. The fix is straightforward: remember Excel starts counting at 1.
3. LEFT, RIGHT, and MID always return text — even when the source cell contains a number. =LEFT(12345, 2) returns "12", not 12. Feed that result into a calculation and you'll get a zero or an error, depending on the formula. Wrap in the VALUE function to convert it back to a number: =VALUE(LEFT(A2, 2)). This is the edge case that bites analysts at scale, not just beginners.

If you're just getting started with formulas generally, the Excel for Beginners complete starter guide is a good place to build the foundation these functions sit on.

Every time you extract text from a cell in Excel, wrap the result in TRIM before using it anywhere else. You'll never chase a phantom VLOOKUP error again.

---