← All Kits · Excel Kit

Excel Text Functions: LEFT, RIGHT, MID, LEN and TEXTJOIN

Michael Nocito · Updated August 2026 · Every result on this page was run in Excel before it was published

Half of data cleaning is pulling one piece out of a code and gluing pieces back together. This page covers the five functions that do it, with the results printed, and then shows the version that keeps working when the codes change shape. That last part is the difference between a formula you write once and a formula you fix every quarter.

What you do: MID to take from the middle, LEFT and RIGHT from the ends, LEN to count, TEXTJOIN to glue a range back together with a separator. Then, the moment your codes are not all the same length, replace the counted positions with FIND.

The short version. These functions cut and join text by position. Positions you counted by eye break; positions you found with FIND do not.

The five functions, with results

Before the table: A2 holds US-WES-2024. How many characters is that, counting the dashes?

Eleven. Here is each function on that one cell, run in Excel.

Two details in there catch people. LEFT(A2,3) returns US- with the dash, because LEFT counts characters and has no idea a dash is special. And LEN takes one argument only, which is what makes =LEN(A2,3) a wrong answer rather than a slower one.

MID reads as start and length: begin at 4, take 3. Everything else about text slicing in Excel is those two numbers, either typed by you or worked out by another formula.

The day the codes get longer

First: the same =MID(A2,4,3) runs against a new code, USA-WEST-2026. What does it return?

-WE. Not an error, not a blank. Three characters starting at position 4, exactly as instructed, and now those three characters are a dash and two letters. LEFT(A6,3) returns USA, which happens to be right, and RIGHT(A6,4) returns 2026, also right. Only the middle one breaks, and it breaks into something that looks like data.

Say why this is worse than an error before reading on. An error stops a report. A value like -WE flows into a pivot table and shows up as a region nobody has ever heard of, months later, in front of somebody else.

The fix is to stop counting and start looking. FIND returns the position of a character, so the formula can work out the boundaries itself.

=MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1) - FIND("-",A2) - 1)

Read the formula in three parts and it stops being frightening. FIND("-",A2) is 3, so start at 4. FIND("-",A2,FIND("-",A2)+1) is the second dash at 7. Seven minus three minus one is three characters to take. On the longer code the same three parts return 4, 9 and 4, and it takes four characters instead.

In Microsoft 365 there is a version you can read at a glance:

=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")

It returned WES on the short code and WEST on the long one, same as the FIND version. TEXTAFTER takes everything after the first dash, TEXTBEFORE then takes everything before the next one. If your Excel has these functions, use them, and keep the FIND version for workbooks that have to open in older builds.

LEN, and the two spaces nobody can see

Before the example: a lookup on a name column keeps returning #N/A even though the name is clearly there in both sheets. What is the first thing to check?

Whether the cell holds what it appears to. A cell reading Alvarez returned LEN of 9, and LEN(TRIM(A7)) returned 7. There are two trailing spaces in it, and nothing on screen will ever show you that.

=LEN(A7)-LEN(TRIM(A7))     ' how many extra spaces are in this cell

Run that down a column before you debug a single lookup. Any number above zero means whitespace, and whitespace means an exact match will fail. The full cleanup routine, including the non-breaking space that TRIM cannot remove, is in cleaning messy data.

This is LEN's real job. Counting characters for a slice is the textbook use, and finding the invisible difference between two cells that look identical is the one you will use every week.

Joining: TEXTJOIN, CONCAT and the ampersand

First: you need all the codes in A2:A5 in one cell, separated by commas, with blanks skipped. Which function is built for that?

TEXTJOIN, and it is the only one of the three that takes a separator and a skip-blanks switch.

The arguments are delimiter, then TRUE or FALSE for ignoring empty cells, then the range. TRUE is what you want almost always, because it stops a gap in the data producing two commas in a row.

CONCAT takes a range but no separator, so it runs everything together. The ampersand, as in =A2&", "&A3, works fine for two or three cells and becomes unreadable at ten. Use TEXTJOIN for a range, an ampersand for a couple of pieces, and CONCAT rarely.

Picture the last time you needed a comma separated list of ids to paste into a query or a ticket. That is a TEXTJOIN and about fifteen seconds.

Edge cases worth knowing

Text functions return text, even when it looks like a number. =ISNUMBER(RIGHT(A2,4)) returns FALSE. Multiply by 1 and =ISNUMBER(RIGHT(A2,4)*1) returns TRUE. So a year pulled out with RIGHT will not sort or filter as a number until you convert it, which is the same problem covered in Paste Special.

FIND is case sensitive, SEARCH is not. =SEARCH("wes",A2) returned 4. =FIND("wes",A2) returned #VALUE!, because the cell holds WES in capitals. SEARCH also accepts the wildcards ? and *. Use SEARCH unless case is part of what you are matching.

A missing character is an error, not a zero. Both FIND and SEARCH return #VALUE! when the thing is not there, so any formula built on them needs wrapping: =IFERROR(your_formula,""). That is covered in IFERROR.

Ask for more characters than exist and you get what there is. LEFT and RIGHT do not complain if you ask for 50 characters from an eight character cell. MID starting past the end returns empty text. Neither is an error, so neither will tell you the data changed.

Text to Columns and Flash Fill do the same job without formulas. Splitting on a delimiter once, for a one-off cleanup, is faster on the Data tab. Formulas earn their place when the file arrives again next month.

Why position-based formulas break

Every one of these functions works on positions, and a position is a fact about one particular value rather than about your data.

When you write MID(A2,4,3), you are not saying "the region code". You are saying "characters four to six", and those happen to be the region code in the rows you were looking at when you wrote it. The formula has no idea what a region code is, so it cannot notice when one stops being three letters long.

FIND changes what the formula is anchored to. It says "the part between the separators", which is a fact about the format rather than about one row. Formats change less often than values do, which is why the FIND version survives the day somebody adds a country with a longer code.

The general habit underneath: when a formula contains a number you counted by looking at the data, ask what makes that number true, and whether the data can stop making it true without telling you.

How to apply this to your own work

1. Find every formula in your workbook with a typed position in it, like MID(x,4,3) or LEFT(x,2). Those are the ones that break quietly.
2. For each one, ask whether every value in the column really has that shape. Sort the column by length with =LEN() and look at both ends.
3. Rewrite anything that fails the length test with FIND, or with TEXTBEFORE and TEXTAFTER if your Excel has them.
4. Wrap anything using FIND or SEARCH in IFERROR, so a missing separator shows as blank rather than as #VALUE! across the report.
5. Before debugging any failed lookup, run =LEN(cell)-LEN(TRIM(cell)) on both sides. It takes ten seconds and it is the cause more often than anything else.

If you have paper nearby, write out one of your own codes and number the characters underneath it. Then write the numbers your formulas assume. Seeing the assumed positions next to the real ones is usually enough to spot which formula is one bad export from being wrong.

Cheat sheet

The one habit to keep

Never type a position into a text formula without checking the length of the whole column first. A counted position is a promise about data you have not seen yet, and the day it breaks it returns something that looks like an answer.

Which code column in your workbooks comes from a system somebody else controls, and what happens to your formulas the week they add a longer code?