Home › Excel functions › LEFT, RIGHT & MID

LEFT, RIGHT and MID in Excel: Extract Text with Examples

LEFT, RIGHT and MID extract part of a text: a number of characters from the start, from the end, or from somewhere in the middle. Combined with FIND, they can split names, codes and addresses into separate columns.

Excel CSE · 3 min read
In this guide
  1. Syntax
  2. Basic example
  3. Split first and last names
  4. TEXTBEFORE / TEXTAFTER
  5. Getting real numbers
  6. Common errors
  7. FAQ

Syntax

FunctionSyntaxReturns
LEFT=LEFT(text, [num_chars])Characters from the start. Without num_chars: 1 character.
RIGHT=RIGHT(text, [num_chars])Characters from the end. Without num_chars: 1 character.
MID=MID(text, start_num, num_chars)num_chars characters, starting at position start_num.

Basic example: split a product code

ABCD
1CodeCategoryNumberCountry
2P-104-NLP104NL
3K-220-DEK220DE
  • =LEFT(A2, 1) returns P: the first character.
  • =MID(A2, 3, 3) returns 104: three characters starting at position 3.
  • =RIGHT(A2, 2) returns NL: the last two characters.

This works well when every code has the same structure. When the parts have different lengths, you need FIND.

Split first and last names with FIND

FIND returns the position of a character. In "Anna Jansen", the space is at position 5.

  • First name: =LEFT(A2, FIND(" ", A2) - 1) takes everything before the space: Anna.
  • Last name: =MID(A2, FIND(" ", A2) + 1, 100) takes everything after the space: Jansen. The 100 just means "the rest"; MID stops at the end of the text.

The same pattern works for anything with a separator, such as the part of an email address before the @: =LEFT(A2, FIND("@", A2) - 1).

Easier in new Excel: TEXTBEFORE and TEXTAFTER

In Microsoft 365 and Excel 2024 you can skip the FIND arithmetic:

  • =TEXTBEFORE(A2, " ") returns Anna
  • =TEXTAFTER(A2, " ") returns Jansen
  • =TEXTSPLIT(A2, "-") splits "P-104-NL" into three cells at once

Without formulas, Data › Text to Columns or Flash Fill (Ctrl+E) can also split a column in one go.

Extracted numbers are text

LEFT, RIGHT and MID always return text. The "104" from the example looks like a number but won't add up in a SUM, and won't match the number 104 in a lookup. Convert it with VALUE or a double minus:

=VALUE(MID(A2, 3, 3))

or =--MID(A2, 3, 3).

Common errors

  • #VALUE! with FIND: the character you're looking for isn't in the text, for example a name without a space. Wrap it in IFERROR: =IFERROR(LEFT(A2, FIND(" ", A2) - 1), A2).
  • An extra space in the result: the source has double spaces. Clean it first with TRIM.
  • Results don't add up: they're text. Use VALUE.

Frequently asked questions

How do I get the last word of a cell?

In Microsoft 365 or Excel 2024, use =TEXTAFTER(A2, " ", -1). The -1 means "after the last space".

How do I remove the first few characters?

Use RIGHT with the length minus the characters to remove: =RIGHT(A2, LEN(A2) - 3) removes the first 3 characters.

What's the difference between FIND and SEARCH?

Both return the position of text. FIND is case-sensitive and doesn't allow wildcards; SEARCH is not case-sensitive and does allow wildcards.

Can LEFT return a number?

No, it always returns text. Wrap it in VALUE to turn the result into a number.