Text functions in Excel take a piece of text, such as a name, a product code or an email address, and cut it up, search it, clean it or join it with others. LEFT, MID and RIGHT take characters out of a text, FIND tells you where something is, SUBSTITUTE replaces it, TRIM cleans it, and TEXTJOIN or & glues pieces together. Text is written inside double quotes in a formula, for example "Pune".
They are what you reach for when data arrives in the wrong shape: a full name in one cell that must be two, codes with a prefix to strip, or customer names with stray spaces. Every result below was produced by running the formula in Microsoft Excel.
In this guide
The short version
LEFT(text, n),RIGHT(text, n)andMID(text, start, n)cut out characters. They always return text, even when the piece looks like a number.FINDis case sensitive,SEARCHis not and accepts wildcards. Both return #VALUE! when nothing is found.SUBSTITUTEreplaces by matching text,REPLACEreplaces by position.TRIMremoves extra spaces but not the non-breaking space that web data often contains.
LEN, LEFT, RIGHT and MID
LEN(text) counts characters, spaces included. LEFT(text, n) takes the first n characters, RIGHT(text, n) the last n, and MID(text, start, n) takes n characters beginning at position start. A1 holds the invoice code INV-2024-0042:
| Formula | Excel shows | Why |
|---|---|---|
| =LEN(A1) | 13 | 13 characters including the two hyphens |
| =LEFT(A1,3) | INV | the first three characters |
| =RIGHT(A1,4) | 0042 | keeps the leading zeros |
| =MID(A1,5,4) | 2024 | start at character 5 and take 4 |
| =LEFT(A1,3)&"/"&RIGHT(A1,4) | INV/0042 | & joins pieces together |
| =RIGHT(A1,4)+1 | 43 | 0042 is converted to a number for the addition |
| =LEFT(12345,2) | 12 | shown as 12, but it is text |
| =ISNUMBER(LEFT(12345,2)) | FALSE | text, not a number |
| =ISNUMBER(–LEFT(12345,2)) | TRUE | the double minus turns it into a real number |
FIND and SEARCH: locate text
FIND(what, where) returns the position of the first match, counting from 1. SEARCH does the same but ignores upper or lower case and understands the wildcards ? (any one character) and * (any run of characters). If the text is not there, both return #VALUE!. Combined with LEFT and MID, they split text at a marker such as @:
| Formula | Excel shows | Why |
|---|---|---|
| =FIND("@",A1) | 5 | the @ is the 5th character |
| =LEFT(A1,FIND("@",A1)-1) | ravi | everything before the @ |
| =MID(A1,FIND("@",A1)+1,99) | upskly.com | everything after the @ (99 is just a big enough length) |
| =FIND("V","ravi") | #VALUE! | FIND is case sensitive: no capital V in ravi |
| =SEARCH("V","ravi") | 3 | SEARCH ignores case |
| =SEARCH("r?v","ravi") | 1 | ? stands for any one character |
| =IFERROR(FIND("#",A1),"not found") | not found | wrap it in IFERROR when the text may be missing |
Splitting a name or an email
The classic job: a full name in one cell, first and last names wanted in two. Find the space, take what is left of it and what is right of it:
| Formula | Excel shows | Why |
|---|---|---|
| =LEFT(A1,FIND(" ",A1)-1) | Asha | first name: everything before the space |
| =MID(A1,FIND(" ",A1)+1,99) | Verma | last name: everything after the space |
| =LEFT(A1,1)&". "&MID(A1,FIND(" ",A1)+1,99) | A. Verma | initial plus last name |
| =TEXTBEFORE(A1," ") | Asha | Microsoft 365 only: same result, no arithmetic |
| =TEXTAFTER(A1," ") | Verma | Microsoft 365 only |
In Microsoft 365 there is also TEXTSPLIT, which splits a text into several cells at once. It needs empty cells to its right and below, because the result spills:
A3 holds a TEXTSPLIT formula and spills into B3 and C3
| A | B | C | |
|---|---|---|---|
| 1 | Asha Verma Pune | ||
| 2 | |||
| 3 | Asha | Verma | Pune |
Only A3 holds a formula
| A | B | C | |
|---|---|---|---|
| 1 | Asha Verma Pune | ||
| 2 | |||
| 3 | =TEXTSPLIT(A1,” “) | Verma | Pune |
Data, Text to Columns and Flash Fill (Ctrl + E) do the same job without formulas. Text to Columns is fixed once you run it, while formulas update when the source changes. Check Flash Fill results, since it works by guessing your pattern.
SUBSTITUTE and REPLACE
SUBSTITUTE(text, old, new, [instance]) replaces text it finds, all matches or only the nth one, and it is case sensitive. REPLACE(text, start, length, new) swaps the characters at a position, whatever they are:
| Formula | Excel shows | Why |
|---|---|---|
| =SUBSTITUTE(A1,"-","/") | 2024/05/17 | every hyphen in 2024-05-17 becomes a slash |
| =SUBSTITUTE(A2,"-","",2) | a-bc-d | only the second hyphen is removed |
| =REPLACE(A3,5,4,"2025") | INV-2025-0042 | characters 5 to 8 of INV-2024-0042 are replaced |
| =SUBSTITUTE(A4,"apple","X") | Apple X | case sensitive: the capital-A Apple is left alone |
| =SUBSTITUTE(A1,"-","")+0 | 20240517 | removing the hyphens gives text that + turns into the number 20240517 |
TRIM, CLEAN and changing case
Imported data is often messy. TRIM removes leading and trailing spaces and reduces runs of spaces inside the text to one. CLEAN removes line breaks and other non-printing characters. UPPER, LOWER and PROPER change case. Here A1 has extra spaces, A2 has a non-breaking space between the names, A3 contains a line break, A4 has mixed case and A5 is a name with an apostrophe:
| Formula | Excel shows | Why |
|---|---|---|
| ="["&TRIM(A1)&"]" | [Asha Verma] | spaces at both ends and doubled spaces are gone (brackets show the ends) |
| =LEN(A1) | 16 | 16 characters before cleaning |
| =LEN(TRIM(A1)) | 10 | 10 after TRIM |
| =LEN(A2) | 10 | Asha, a non-breaking space and Verma |
| =LEN(TRIM(A2)) | 10 | TRIM changed nothing: it does not touch the non-breaking space |
| =LEN(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) | 10 | swap the non-breaking space for a normal one first |
| =LEN(A3) | 3 | a, the line break and b |
| =LEN(CLEAN(A3)) | 2 | CLEAN removed the line break |
| =UPPER(A4) | ASHA VERMA | |
| =LOWER(A4) | asha verma | |
| =PROPER(A4) | Asha Verma | first letter of each word in capitals |
| =PROPER(A5) | O'Neil Mcdonald | PROPER capitalises after the apostrophe and misses the Mc rule |
The non-breaking space, CHAR(160), is the most common reason why a cleaned value still does not match. It looks like a space, but TRIM and a search for a normal space both miss it:
| Formula | Excel shows | Why |
|---|---|---|
| =FIND(" ",A1) | #VALUE! | no normal space in the text |
| =FIND(CHAR(160),A1) | 5 | the non-breaking space is at position 5 |
| =A1="Asha Verma" | FALSE | so it does not equal the text typed with a normal space |
| =SUBSTITUTE(A1,CHAR(160)," ")="Asha Verma" | TRUE | after SUBSTITUTE it does |
Joining text: &, CONCAT and TEXTJOIN
The & operator joins texts. CONCAT joins pieces and ranges. TEXTJOIN(delimiter, ignore_empty, range) joins a whole range with a separator between the items, and can skip empty cells. CONCAT and TEXTJOIN need Excel 2019 or Microsoft 365. Here A1:B4 hold names and marks, and row 5 is empty:
| Formula | Excel shows | Why |
|---|---|---|
| =A1&" scored "&B1 | Asha scored 82 | |
| =CONCAT(A1," – ",B1) | Asha – 82 | |
| =TEXTJOIN(", ",TRUE,A1:A5) | Asha, Ravi, Meera, John | TRUE means skip empty cells |
| =TEXTJOIN(", ",FALSE,A1:A5) | Asha, Ravi, Meera, John, | FALSE keeps the empty cell, leaving a stray separator at the end |
| =TEXTJOIN(", ",TRUE,IF(B1:B4>=50,A1:A4,"")) | Asha, Meera | names of students with 50 or more |
| =TEXTJOIN(", ",TRUE,IF(B1:B4>=50,A1:A4)) | Asha, FALSE, Meera, FALSE | without the empty text as the false value, IF puts the word FALSE in the list |
Counting characters and words
Excel has no built-in word counter, but the difference between the length of a text and the length with the target removed tells you how many times it occurs. A1 holds the quick brown fox with a double space, A2 holds banana and A3 holds Banana:
| Formula | Excel shows | Why |
|---|---|---|
| =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1 | 4 | words: count the spaces after TRIM, then add one |
| =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")) | 3 | how many a in banana |
| =LEN(A3)-LEN(SUBSTITUTE(A3,"a","")) | 3 | SUBSTITUTE is case sensitive: the capital B does not matter, and the count is still 3 here |
| =(LEN(A3)-LEN(SUBSTITUTE(LOWER(A3),"a",""))) | 3 | lowering the case first makes the count case-insensitive |
| =LEN(A2)-LEN(SUBSTITUTE(A2,"an","")) | 4 | for a longer target the difference is 4 characters, not 2 occurrences |
| =(LEN(A2)-LEN(SUBSTITUTE(A2,"an","")))/LEN("an") | 2 | divide by the length of the target |
A few more worth knowing
| Formula | Excel shows | Why |
|---|---|---|
| =EXACT(A1,A2) | FALSE | EXACT is the case-sensitive comparison |
| =A1=A2 | TRUE | the = operator ignores case |
| =REPT("*",5) | ***** | repeat a text |
| =REPT("|",A4/10) | |||| | a bar made of characters, one per 10 units |
| =VALUE(A3)+1 | 13.5 | VALUE converts text that looks like a number |
| =TEXT(A3,"0.00") | 12.50 | format as text |
| =UPPER(LEFT(A1,1))&MID(A1,2,99) | Abc | capitalise only the first letter of the text |
| =CODE("A") | 65 | character code of A |
| =CHAR(66) | B | the character with code 66 |
Common mistakes
Mistake 1: forgetting that the pieces are text
LEFT, MID and RIGHT return text even when the piece looks like a number, so SUM ignores it. Convert with VALUE() or -- when you need to calculate.
Mistake 2: a hidden non-breaking space
If TRIM seems to do nothing, or a lookup fails for a value that looks identical, test with LEN before and after. If the length does not drop, look for CHAR(160).
Mistake 3: hard-coding the position
=LEFT(A1,4) works only while every first name has four letters. Find the marker with FIND and calculate the length from it, as in the splitting examples.
Mistake 4: FIND on text that may be missing
When the marker is not in the text, FIND returns #VALUE! and so does everything built on it. Wrap it: IFERROR(..., "").
Cheat sheet
| Task | Formula | Note |
|---|---|---|
| First n characters | =LEFT(A1, n) | returns text |
| Last n characters | =RIGHT(A1, n) | |
| From the middle | =MID(A1, start, n) | start counts from 1 |
| Position of a character | =FIND("@", A1) | case sensitive; SEARCH is not |
| Text before a marker | =LEFT(A1, FIND(" ", A1) – 1) | or TEXTBEFORE in Microsoft 365 |
| Text after a marker | =MID(A1, FIND(" ", A1) + 1, 99) | or TEXTAFTER in Microsoft 365 |
| Replace by matching | =SUBSTITUTE(A1, "-", "") | case sensitive |
| Replace by position | =REPLACE(A1, 5, 4, "2025") | |
| Remove extra spaces | =TRIM(A1) | not CHAR(160) |
| Join with a separator | =TEXTJOIN(", ", TRUE, A1:A9) | Excel 2019 or 365 |
| Count a character | =LEN(A1)-LEN(SUBSTITUTE(A1, "a", "")) | |
| Count words | =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1), " ", ""))+1 |
Try it yourself
Work out each answer first, then open the solution.
1. A column holds email addresses. Write a formula that returns the domain, the part after the @, and test it on two addresses.
Show solution
| Formula | Excel shows |
|---|---|
| =MID(A1,FIND("@",A1)+1,99) | upskly.com |
| =MID(A2,FIND("@",A2)+1,99) | example.org |
Find the @, then take everything after it. The 99 is only a length that is longer than any domain.
2. A1 holds INV-2024-0042. Return INV/0042, and separately return the code with all hyphens removed.
Show solution
| Formula | Excel shows |
|---|---|
| =LEFT(A1,3)&"/"&RIGHT(A1,4) | INV/0042 |
| =SUBSTITUTE(A1,"-","") | INV20240042 |
3. Count the words in a sentence held in A1.
Show solution
| Formula | Excel shows |
|---|---|
| =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1 | 4 |
Count the spaces (length with spaces minus length without) and add one. TRIM first, so extra spaces are not counted.
4. A1 holds the text of a 16-digit card number. Show only the last four digits, and replace the rest with stars.
Show solution
| Formula | Excel shows |
|---|---|
| =REPT("*",LEN(A1)-4)&RIGHT(A1,4) | ************5678 |
Repeat a star for every character except the last four, then join the last four. Card numbers are 16 digits, so store them as text in the first place (a number would lose its last digit).
Frequently asked questions
How do I extract text from a cell in Excel?
Use LEFT(text, n) for the first characters, RIGHT(text, n) for the last ones and MID(text, start, n) for characters from the middle. Combine them with FIND to cut at a marker such as a space or @.
What is the difference between FIND and SEARCH in Excel?
FIND is case sensitive and takes no wildcards. SEARCH is not case sensitive and accepts ? and *. Both return #VALUE! when the text is not found.
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE replaces text that matches, anywhere it appears. REPLACE replaces the characters at a given position and length, whatever they are.
Why is TRIM not removing spaces in Excel?
The spaces are probably non-breaking spaces (CHAR(160)), which come from web pages. Use TRIM(SUBSTITUTE(A1, CHAR(160), " ")).
How do I combine text from several cells in Excel?
Use & for a few cells, or TEXTJOIN(", ", TRUE, A1:A9) for a range with a separator. TEXTJOIN needs Excel 2019 or Microsoft 365.
How do I split a full name into first and last name?
First name: =LEFT(A1, FIND(" ", A1) - 1). Last name: =MID(A1, FIND(" ", A1) + 1, 99). In Microsoft 365 you can use TEXTBEFORE, TEXTAFTER or TEXTSPLIT instead.
How do I count how many times a character appears in a cell?
=LEN(A1) - LEN(SUBSTITUTE(A1, "a", "")). Wrap the text in LOWER first to ignore case, and divide by the target’s length when it has more than one character.
Why does LEFT or MID give me a number I cannot add up?
Text functions always return text. Convert the result with VALUE() or a double minus before adding.
Test yourself
Timed questions on Text Functions, with an explanation for every answer.
