How to extract text in Excel: LEFT, RIGHT, MID, and TRIM

To pull part of a text string in Excel, use LEFT, RIGHT, or MID: =LEFT(A2,2) returns the first two characters of A2, =RIGHT(A2,4) the last four, and =MID(A2,4,4) four characters starting at position 4. To extract up to a delimiter such as a space or comma instead of a fixed count, FIND (or SEARCH) locates the delimiter and LEFT or MID cuts there. TRIM clears any extra spaces left behind.

A substring is just part of a longer text string, and Excel has no single SUBSTRING function; you assemble one from a handful of text functions. This page covers LEFT, RIGHT, and MID for extracting by position, LEN for length, FIND and SEARCH for locating a delimiter, the patterns that combine them to pull text before or after that delimiter, TRIM for stray spaces, the case-fixers SUBSTITUTE, PROPER, UPPER, and LOWER, and the newer TEXTBEFORE, TEXTAFTER, and TEXTSPLIT route in Microsoft 365. Turning a number or date into formatted text is a different job, and it belongs to the TEXT function.

Is there a SUBSTRING function in Excel?

No. Some tools have one function named SUBSTRING; Excel splits the work across several. Three of them do the extracting:

• LEFT(text, [num_chars]) reads from the start of the string.
• RIGHT(text, [num_chars]) reads from the end.
• MID(text, start_num, num_chars) reads from a position in the middle.

Two more do the measuring and locating that feed those counts: LEN(text) returns how long a string is, and FIND or SEARCH returns where a character sits inside it. Put together, they cover every substring you are likely to need, whether you know the exact character count in advance or have to work it out from a delimiter.

How do I extract the first or last characters?

For a fixed-width field, a plain character count is enough. LEFT reads from the left edge and returns as many characters as you ask for; leave the count out and it returns one. RIGHT does the same thing from the right edge.

The last-four formula is the mirror image, counted from the other end:

One thing to watch: the serial RIGHT pulls out looks like a number but arrives as text, left aligned in its cell and unable to feed a SUM. That is the same trap as pasted or imported figures, and the fix is in numbers stored as text in Excel.

How do I pull characters from the middle?

MID starts wherever you tell it and takes a set number of characters from there. Its first argument is the text, its second is the starting position (the first character is position 1), and its third is how many characters to return.

When you need the length of a string, LEN counts every character in it, spaces included:

returns 12 for GB-2026-4021, the two hyphens counted along with the letters and digits. LEN rarely stands alone; its job is to feed a count into RIGHT or MID, which the delimiter patterns below rely on.

How do I find where a delimiter is?

Fixed counts break the moment the pieces vary in length, and most real data does: names, email addresses, and free-text codes all change width row to row. FIND solves that by returning the position of a character inside the string, so you can measure the boundary rather than hard-code it. Its arguments are FIND(find_text, within_text, [start_num]): what to look for, where to look, and optionally which character to start from.

That capital-versus-lowercase gap is the practical difference between the two locator functions. Microsoft draws the distinction in one line: “FIND is case-sensitive, and SEARCH is not case-sensitive.” So =SEARCH("m",A2) returns 1, matching the capital M first, while =FIND("m",A2) skips it and returns 6. SEARCH also accepts the wildcards ? and * in its find_text, where a ? stands for any single character and * for any run of them; FIND treats find_text literally. The rule of thumb: reach for SEARCH when case does not matter or you need a wildcard, and for FIND when the capitalization is the point.

How do I extract text before or after a delimiter?

This is where the functions combine. Feed a FIND result into LEFT and you get everything up to the delimiter; feed it into MID or RIGHT and you get everything after it. A single-space full name is the clearest case: the first name ends one character before the space, and the last name starts one character after it.

The -1 is what trims the space itself off the result: FIND returns 5 for the space, and LEFT takes the four characters ahead of it. For the last name, start MID one character past the space and hand it a count large enough to reach the end:

If you would rather count from the right, RIGHT and LEN reach the same result: =RIGHT(A2,LEN(A2)-FIND(" ",A2)) takes the last three characters, since the name is eight characters long and the space is at position 5. To split on a comma instead of a space, change the " " to "," in the FIND. This is the mechanics behind a name split; when the goal is to break a whole column into first and last name columns in one pass, split cells in Excel walks the Text to Columns and Flash Fill routes as well.

How do I remove extra spaces?

Extracted text often carries spaces you did not want, especially when the source was pasted from somewhere else. TRIM cleans them up. Microsoft describes it plainly: it “Removes all spaces from text except for single spaces between words.” So it drops leading and trailing spaces outright and collapses any run of spaces between words down to one.

returns Anna Kim: the leading and trailing spaces gone, and the double space between the names reduced to one. Wrap TRIM around any extraction whose spacing you cannot trust, as in =TRIM(LEFT(A2,FIND(" ",A2)-1)).

There is one limit that catches people out, and it explains most cases where TRIM seems to do nothing. TRIM was built for the ordinary space, character 32. Text copied from web pages often contains a different, invisible character, the nonbreaking space with decimal value 160, and TRIM leaves that one in place. When a value still looks padded after TRIM, a nonbreaking space is the likely culprit, and SUBSTITUTE can strip it out.

What about SUBSTITUTE, PROPER, UPPER, and LOWER?

These four reshape a string rather than cut a piece out of it, and they pair naturally with the extractors.

• SUBSTITUTE replaces one piece of text with another: =SUBSTITUTE(A2,"-","") removes every hyphen from GB-2026-4021, returning GB20264021. Give it a fourth argument to change only the Nth occurrence, so =SUBSTITUTE(A2,"-","",2) touches only the second hyphen. It is a common way to clear out nonbreaking spaces or normalize a delimiter before extracting.
• PROPER capitalizes the first letter of each word and lowercases the rest, so =PROPER("anna kim") returns Anna Kim, handy for names that arrived in a single case.
• UPPER and LOWER force one case throughout: =UPPER("gb") returns GB and =LOWER("KIM") returns kim. LOWER leaves anything that is not a letter untouched.

The reverse of all this extracting, joining separate pieces back into one string, is concatenate in Excel.

The newer route: TEXTBEFORE, TEXTAFTER, and TEXTSPLIT

Excel for Microsoft 365 and Excel 2024, on Windows and Mac, add three functions that take most of the position math off your hands. TEXTBEFORE returns the text before a delimiter and TEXTAFTER the text after it, so the two name formulas above collapse to:

which returns Anna, and =TEXTAFTER(A2," "), which returns Kim, with no FIND, no LEN, and no off-by-one arithmetic. TEXTSPLIT goes further and breaks the whole string at the delimiter at once, spilling the parts across cells. In any version that predates these functions, they return a name error, and the LEFT, MID, and FIND patterns above are the fallback that works everywhere.

The short version

• Excel has no single SUBSTRING function; you build one from LEFT, RIGHT, and MID. =LEFT(A2,2), =RIGHT(A2,4), and =MID(A2,4,4) extract by position.
• To extract up to a delimiter, FIND (or SEARCH) locates the separator and LEFT, MID, or RIGHT cuts there: =LEFT(A2,FIND(" ",A2)-1) is the text before the first space, =MID(A2,FIND(" ",A2)+1,100) the text after it.
• FIND is case sensitive and matches literally; SEARCH ignores case and accepts the wildcards ? and *. LEN counts characters, spaces included, and feeds those counts into RIGHT and MID.
• TRIM clears leading, trailing, and doubled spaces, but not the nonbreaking space (character 160), which SUBSTITUTE can remove. Digits pulled out with RIGHT or MID arrive as text that will not sum.
• Microsoft 365 and Excel 2024 replace the position math with =TEXTBEFORE(A2," ") and =TEXTAFTER(A2," "); to break a whole column at once, split cells in Excel covers the column-wide tools.