How to Extract Text From a Cell in Excel
· Updated · 12 min read
To extract text from a cell in Excel, pick the function by where the text sits. LEFT takes characters from the start, RIGHT from the end and MID from any position in the middle:
=LEFT(text, num_chars)
=RIGHT(text, num_chars)
=MID(text, start_num, num_chars)That covers codes with a fixed layout. When the piece you want sits before or after a character that moves, such as a comma or an @, add FIND or SEARCH to work out the position.
Fixed positions: LEFT, MID and RIGHT
Order references in this table always have the same shape: a four-digit year, a dash, a two-digit month, a dash and a four-digit order number. Because every part starts at the same place, you can count the characters once and reuse the formula on every row.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Reference | Year | Month | Number | Length | Number as a value |
| 2 | 2025-03-0417 | 2025 | 03 | 0417 | 12 | 417 |
| 3 | 2025-07-1182 | 2025 | 07 | 1182 | 12 | 1182 |
| 4 | 2025-11-0093 | 2025 | 11 | 0093 | 12 | 93 |
| 5 | 2026-01-0250 | 2026 | 01 | 0250 | 12 | 250 |
The year in B2 is:
=LEFT(A2, 4)Result: 2025. The month starts at character 6 (four digits, then the dash at position 5), and it is two characters long:
=MID(A2, 6, 2)Result: 03. The order number is the last four characters:
=RIGHT(A2, 4)Result: 0417.
The results are text, not numbers
LEFT, RIGHT and MID always return text, even when the characters are digits. That is why D2 keeps its leading zero. It also means a SUM over column D returns 0, and a lookup against a list of real numbers will not match. When you need a number, wrap the result in VALUE, as in column F:
=VALUE(D2)Result: 417. Multiplying by 1 (=RIGHT(A2,4)*1) does the same thing. Keep the text version when the leading zeros matter, such as for product codes or postcodes.
Check the length first
Fixed positions only work when every value has the same shape. LEN counts the characters in a cell, spaces included. Every reference above is 12 characters long (column E). A row that says 13 probably has a stray space, and its MID and RIGHT results will be off. Our guide on how to remove spaces in Excel covers the fix.
Excel formula to extract text before a character
Names often arrive as "Last, First". The comma is not at the same position on each row, so a fixed number in LEFT will not work. FIND returns the position of one piece of text inside another:
=FIND(find_text, within_text, [start_num])=FIND(",", A2) on "Becker, Tom" returns 7, because the comma is the seventh character. Everything before it is the last name, so take one character fewer than that:
=LEFT(A2, FIND(",", A2) - 1)Result: Becker. Without the - 1 you would get "Becker," with the comma attached.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Contact | Comma at | Last name | First name (MID +2) | First name (safer) |
| 2 | Becker, Tom | 7 | Becker | Tom | Tom |
| 3 | Okafor, Grace | 7 | Okafor | Grace | Grace |
| 4 | Lindqvist, Ana | 10 | Lindqvist | Ana | Ana |
| 5 | Moreau,Jean-Luc | 7 | Moreau | ean-Luc | Jean-Luc |
The sheet keeps the comma position in a helper column (B) so each formula stays short. You can nest FIND inside LEFT and MID instead; the results are the same, but a helper column is easier to check.
Excel formula to extract text after a character
To take everything after the comma, start MID one character after it:
=MID(A2, FIND(",", A2) + 2, LEN(A2))Result: Tom. The + 2 skips the comma and the space after it. The last argument, LEN(A2), asks for more characters than could possibly be left. MID simply stops at the end of the text, so you do not need to work out the exact length of the first name.
Row 5 shows the weak spot. "Moreau,Jean-Luc" has no space after the comma, so + 2 skips the J and returns ean-Luc. Column E is safer: it starts one character after the comma and lets TRIM remove a space if there is one.
=TRIM(MID(A2, FIND(",", A2) + 1, LEN(A2)))Result: Tom on rows 2 to 4 and Jean-Luc on row 5.
The same pattern pulls the domain out of an email address (text after the @) or the product name out of "SKU-123: Copy paper" (text after the colon). Change the character you search for and the number of characters you skip.
You will also see =RIGHT(A2, LEN(A2) - FIND(",", A2) - 1). It gives the same answer with more arithmetic to get wrong.
Newer versions: TEXTBEFORE and TEXTAFTER
Microsoft 365, Excel for the web and Excel 2024 have two functions that do this in one step:
=TEXTBEFORE(A2, ",")
=TEXTAFTER(A2, ", ")They also accept an instance number, so =TEXTAFTER(A2, "-", -1) returns the text after the last dash. If your file is shared with people on older versions, stick to LEFT, MID and FIND, which work everywhere.
First word and last word in a cell
Splitting on a space is the same job as splitting on a comma, with one extra problem: a name or a product can have any number of words. These contacts come from a Brightline Supply Co. CRM import, where one person was entered with a first name only:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Contact | First word | Spaces | Last space at | Last word |
| 2 | Maria Hart | Maria | 1 | 6 | Hart |
| 3 | Kevin Garcia | Kevin | 1 | 6 | Garcia |
| 4 | Maria de la Cruz | Maria | 3 | 12 | Cruz |
| 5 | Jean-Luc Moreau | Jean-Luc | 1 | 9 | Moreau |
| 6 | Chris | Chris | 0 | 0 | Chris |
Excel formula for the first word in a cell
The first word is everything before the first space, so the "text before a character" pattern works:
=LEFT(A2, FIND(" ", A2) - 1)Result: Maria. On row 6, "Chris" has no space, so FIND returns #VALUE!. Column B avoids that by searching a copy of the text with a space added to the end:
=LEFT(A2, FIND(" ", A2 & " ") - 1)A2 & " " guarantees there is always a space to find. For "Maria Hart" the first space is still at position 6, so the result is the same. For "Chris" the only space is the one just added, at position 6, and LEFT returns the whole word Chris.
Excel find last space in string
FIND reads from left to right and always returns the first match, and in older versions there is no "find from the right" function. The usual workaround is to mark the last space with a character that never appears in the data, then FIND the marker. It takes two steps.
Count the spaces by comparing the length with and without them (column C):
=LEN(A2) - LEN(SUBSTITUTE(A2, " ", ""))"Maria de la Cruz" has 3 spaces. SUBSTITUTE has an optional fourth argument, the instance number, which replaces only that occurrence. Replacing the third space with | gives "Maria de la|Cruz", and FIND returns its position (column D):
=FIND("|", SUBSTITUTE(A2, " ", "|", C2))Result: 12. On row 6 there are 0 spaces, SUBSTITUTE with instance 0 returns #VALUE!, and the IFERROR in column D turns that into 0, which means "start at the beginning".
Extract the last word in Excel
Once you know where the last space is, the last word starts one character after it. MID takes it from there to the end (column E):
=MID(A2, D2 + 1, LEN(A2))Results: Hart, Garcia, Cruz, Moreau and, on row 6, Chris. The hyphen in Jean-Luc is not a space, so it stays inside the first word.
You will often see a one-cell version that needs no helper columns:
=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))It replaces every space with 100 spaces, so the last 100 characters of the result hold some padding and the whole last word, and nothing from earlier words. RIGHT takes those 100 characters and TRIM strips the padding. It works on one-word cells too. The only limit is a last word longer than 100 characters; raise both 100s if your data could have one. It is shorter to type and harder to check, so the helper columns are the better choice when someone else will review the sheet.
In Microsoft 365, Excel for the web and Excel 2024, TEXTBEFORE and TEXTAFTER do both jobs directly:
=TEXTBEFORE(A2, " ")
=TEXTAFTER(A2, " ", -1)The -1 instance number means "the last space". Both return #N/A when the cell has no space. Their sixth argument sets what to return instead, so =TEXTAFTER(A2, " ", -1, , , A2) returns the whole cell for a one-word name.
The second word: how to use the MID function with FIND
MID needs a start position and a length. For a word in the middle, both come from FIND. FIND's optional third argument, start_num, tells it where to begin looking, so the second space is:
=FIND(" ", A2, FIND(" ", A2) + 1)For "Maria de la Cruz" the first space is at 6 and the second at 9. The second word starts after the first space and runs up to the second one:
=MID(A2, FIND(" ", A2) + 1, FIND(" ", A2, FIND(" ", A2) + 1) - FIND(" ", A2) - 1)Result: de. Reading the arguments in order: start at 6 + 1 = 7, and take 9 - 6 - 1 = 2 characters. The formula returns #VALUE! on a name with only one space, because there is no second space to find. In Microsoft 365, =TEXTBEFORE(TEXTAFTER(A2, " "), " ") gives the same result in a shorter form.
FIND vs SEARCH
Both return a position, and both return #VALUE! when the text is not there. There are two differences:
| FIND | SEARCH | |
|---|---|---|
| Upper and lower case | Must match exactly | Ignored |
Wildcards * and ? | Treated as normal characters | Work as wildcards |
| A | B | C | |
|---|---|---|---|
| 1 | Note | FIND "urgent" | SEARCH "urgent" |
| 2 | Urgent: call back today | #VALUE! | 1 |
| 3 | Customer marked this urgent | 22 | 22 |
| 4 | URGENT reorder of paper | #VALUE! | 1 |
| 5 | Call before noon | #VALUE! | #VALUE! |
FIND only finds the lower-case word in row 3 (position 22). SEARCH finds it in rows 2, 3 and 4 (positions 1, 22 and 1). Neither finds it in row 5, so both return #VALUE! there.
For splitting on a comma, a dash or an @, either function works. To check whether a note mentions a word, SEARCH is usually right, because people type in any case; ISNUMBER turns its position into TRUE or FALSE. To count the rows that contain a word, COUNTIF with wildcards is simpler: see COUNTIF in Excel.
Common mistakes
Off by one around the delimiter. FIND(",", A2) is the position of the comma itself. Before the comma you need - 1; after it you need + 1, plus one more for each space you want to skip. If your results keep a stray comma or lose a first letter, check these numbers first.
A hard-coded length where the text varies. An AI assistant asked for "the last name" may write =LEFT(A2, 6) because the first row is "Becker". It looks right on that row and cuts "Lindqvist" to "Lindqv". Whenever the length of the part changes from row to row, the formula needs FIND, not a fixed number. Test any text formula on the longest and shortest values in the column, not just the first row.
Missing delimiters. If one row has no comma, FIND returns #VALUE! and so does everything built on it. Decide what that row should show. =IFERROR(LEFT(A2, FIND(",", A2) - 1), A2) returns the whole cell when there is no comma. Use that only once you know why the comma is missing; our IFERROR guide explains why a blanket fallback can hide problems.
Treating extracted digits as numbers. A VLOOKUP of the text "0417" against the number 417 returns #N/A. Convert with VALUE, or keep both sides as text. Our VLOOKUP guide covers other reasons a lookup fails.
FAQ
How do I extract the first few characters of a cell?
Use LEFT with the number of characters: =LEFT(A2, 4) returns the first four characters of A2.
How do I extract text after a specific character in Excel?
Find the character, then start MID after it: =MID(A2, FIND(",", A2) + 1, LEN(A2)). Wrap it in TRIM if a space may follow the character. In Microsoft 365 you can use =TEXTAFTER(A2, ",").
How do I extract text before a character?
Use =LEFT(A2, FIND(",", A2) - 1). Replace the comma with whatever character marks the end of the part you want.
How do I get the first word before a space in Excel?
Use =LEFT(A2, FIND(" ", A2 & " ") - 1). Adding a space to the end of the text means a one-word cell returns the whole word instead of #VALUE!. In Microsoft 365, =TEXTBEFORE(A2, " ") also works.
How do I extract the last word in Excel?
Use =TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100)), or find the last space with FIND("|", SUBSTITUTE(A2, " ", "|", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))) and take the text after it with MID. In Microsoft 365, =TEXTAFTER(A2, " ", -1) returns the last word directly.
How do I find the last space in a string?
Count the spaces with LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")), replace only that occurrence with a marker using SUBSTITUTE's fourth argument, then FIND the marker. The full formula is in the answer above.
What is the difference between FIND and SEARCH in Excel?
FIND is case sensitive and does not accept wildcards. SEARCH ignores case and accepts * and ?. Both return the position of the text, or #VALUE! if it is not found.
Why does my extracted number not add up?
LEFT, RIGHT and MID return text. Convert the result with =VALUE(...) before you sum it or look it up against real numbers.
What is the opposite of extracting text?
Joining it back together. See how to concatenate in Excel for &, CONCAT and TEXTJOIN.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Pull the category code from a SKU
- Cut the product number out of the middle
- Find each contact’s company domain
- The AI found no VIP accountsAudit