Text Functions in Excel: LEFT, RIGHT, MID and TEXTJOIN Explained

LEFT, RIGHT and MID pull out specific characters from a piece of text based on position, while CONCATENATE and TEXTJOIN combine separate pieces of text back together into one. Between the two groups, they cover almost everything you'll ever need to reshape messy or combined text without retyping anything by hand.

For a long stretch early on, I built mailing labels for client letters by manually copying each address piece — name, street, suburb, postcode — one at a time into a single cell, retyping commas and spaces between them by hand, client by client. It genuinely didn't occur to me that Excel could stitch text together with a formula, because nobody had ever shown me, and I'd never gone looking. Someone finally saw me doing this for what must have been the fortieth label that afternoon and asked, not unkindly, whether I'd heard of the ampersand symbol. That one small nudge saved me an embarrassing number of afternoons after that.

How do LEFT, RIGHT and MID actually work?

Each one pulls a specific chunk of characters out of a text string, based on position rather than content.

=LEFT(text, number_of_characters) — pulls from the start =RIGHT(text, number_of_characters) — pulls from the end =MID(text, start_position, number_of_characters) — pulls from the middle

Extracting the first three letters of a product code as a category prefix:

=LEFT(A2, 3)

If A2 contains "NSW-4821-B", this returns "NSW".

Extracting the middle section, say the numeric part of that same code, starting at character 5 for 4 characters:

=MID(A2, 5, 4)

This returns "4821" from the same example.

How do I combine LEFT, RIGHT or MID with FIND to handle text of varying length?

The examples above work fine when every entry follows exactly the same pattern. Real data rarely does — names, addresses, and codes often vary in length, which means a fixed character count breaks as soon as something's shorter or longer than expected.

FIND locates the position of a specific character within text, which you can then feed into LEFT, RIGHT or MID instead of a fixed number.

Extracting everything before the first hyphen, regardless of how long that first section is:

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

FIND locates where the hyphen sits, and subtracting 1 stops LEFT from including the hyphen itself in the result. This is far more reliable than a fixed character count whenever your data isn't perfectly uniform in length.

What's the difference between CONCATENATE and the & symbol?

Both join text together, and for basic use they behave the same way. CONCATENATE is a proper function; the ampersand (&) is a shorthand operator that does the same job with less typing.

=CONCATENATE(A2, " ", B2)

versus

=A2&" "&B2

Both combine a first name (A2) and last name (B2) with a space between them. Most people I know, myself included, default to the ampersand simply because it's shorter to type, though CONCATENATE can be slightly clearer to read for someone unfamiliar with the & shorthand.

Why is TEXTJOIN usually better than CONCATENATE for longer combinations?

TEXTJOIN lets you combine several pieces of text at once, with a shared delimiter (like a comma or space) applied automatically between each piece, and it can skip blank cells entirely.

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Building a full mailing address from separate columns, skipping any that happen to be blank (like a missing unit number):

=TEXTJOIN(", ", TRUE, A2, B2, C2, D2)

The TRUE for ignore_empty means if, say, B2 (a unit number) is blank for a particular client, TEXTJOIN skips it cleanly rather than leaving an awkward double comma where CONCATENATE or & would. This alone would've saved me a fair amount of manual clean-up on those mailing labels years ago.

If you've read my post on  "cleaning messy data" , it's worth wrapping TEXTJOIN's inputs in TRIM first if any of the source columns might have stray spaces, since TEXTJOIN will faithfully combine those spaces right along with everything else.

Why does my LEFT, RIGHT or MID formula return #VALUE!?

A few common causes:

1. The source cell isn't actually text, or contains a number Excel is treating differently than expected — wrapping the reference in TEXT() first sometimes resolves this.
2. The character count argument is negative or missing, which none of these functions can handle.
3. FIND couldn't locate the character you're searching for, if you're combining it with LEFT/RIGHT/MID as shown above — if there's no hyphen in a particular cell, FIND returns an error, which then breaks the whole nested formula.

Can I combine these text functions with a lookup like XLOOKUP?

Yes, and it's a genuinely handy combination for messy source data. Say a client ID column contains extra formatting you need to strip out before a lookup will match correctly:

=XLOOKUP(TRIM(LEFT(A2, FIND("-", A2) - 1)), ClientTable[Client ID], ClientTable[Account Manager])

This extracts the clean portion of the ID first, then feeds that cleaned value straight into the lookup, rather than needing a separate helper column to hold the cleaned version.

FAQ

Can TEXTJOIN handle an entire range instead of listing each cell separately? Yes — =TEXTJOIN(", ", TRUE, A2:A10) works just as well as listing individual cells, and is much quicker to write for a longer range.

What's the difference between TEXTJOIN and CONCAT? CONCAT (a simpler, newer version of CONCATENATE) joins ranges together without a delimiter option, while TEXTJOIN adds the ability to insert a consistent separator between each piece and skip blanks. TEXTJOIN is generally the more useful of the two for real-world use.

Can I use LEFT, RIGHT or MID on numbers, not just text? Yes, though Excel treats the result as text afterwards, even if it looks like a number. If you need to do maths on the result, wrap it in VALUE() to convert it back to a genuine number.

Why does MID sometimes return fewer characters than I asked for? If the requested character count runs past the end of the text string, MID simply returns whatever's left rather than throwing an error — this is expected behaviour, not a bug.

Is there a limit to how many items TEXTJOIN can combine? Up to 252 separate text arguments, which is far beyond what most everyday formulas will ever need.

Should I use these text functions or Power Query for reshaping messy data? For a one-off formula-based fix on a moderate amount of data, these functions are quicker to set up. If you've read my post on "FILTER, SORT and UNIQUE", a similar trade-off applies here — for genuinely large, recurring data transformations, a dedicated tool built for reshaping data at scale becomes worth learning, but for everyday spreadsheet work, text functions handle the vast majority of cases just fine.