Excel text functions: split, join and clean up text
Text functions pull pieces out of a cell, put pieces together or tidy up messy data. Think of reading the product group from an item code or building email addresses from a staff list.
In short: =LEFT(A2,3) returns the first three characters, =RIGHT(A2,4) the last four and =MID(A2,5,4) four characters starting at position 5. The & operator joins text.
Try it now
Every item code starts with a short prefix for its product group. Pull that prefix out with a formula. Your formula is then filled down for every row.
Task The first three letters of each item code identify the product group. In C2, write a formula that extracts this prefix. The formula is filled down to C7.
| A | B | C | |
|---|---|---|---|
| 1 | Item code | Item | Group |
| 2 | PAP-1042 | Copy paper A4 | |
| 3 | TEC-2210 | USB stick 64 GB | |
| 4 | FUR-3305 | Office chair | |
| 5 | PAP-1187 | Envelopes | |
| 6 | TEC-2391 | Webcam | |
| 7 | FUR-3420 | Drawer unit |
The text functions you will use most
| Function | What it does | Example with A2 = "PAP-1042" |
|---|---|---|
| LEFT | Characters from the start | =LEFT(A2,3) returns PAP |
| RIGHT | Characters from the end | =RIGHT(A2,4) returns 1042 |
| MID | Characters from a given position | =MID(A2,5,2) returns 10 |
| LEN | Number of characters | =LEN(A2) returns 8 |
| FIND / SEARCH | Position of a character or word | =FIND("-",A2) returns 4 |
| TRIM | Removes leading and trailing spaces and collapses double spaces | =TRIM(A2) |
| UPPER / LOWER / PROPER | All caps, all lowercase, first letter of each word capitalized | =PROPER("anna weber") returns Anna Weber |
| SUBSTITUTE | Replaces one piece of text with another | =SUBSTITUTE(A2,"-","/") returns PAP/1042 |
Splitting text at a delimiter
LEFT and RIGHT only work when every piece has the same length. Names rarely do. "Anna Weber" and "Jonas Hoffmann" need a formula that first finds the space with FIND and then counts from there:
- First name:
=LEFT(A2,FIND(" ",A2)-1) - Last name:
=MID(A2,FIND(" ",A2)+1,100) - Or:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
The 100 in MID is just a number longer than any last name. MID never returns more characters than exist, so overshooting is safe. FIND is case-sensitive. SEARCH ignores case and accepts the wildcards * and ?. For text before, after and between characters with practice, see MID, FIND and SEARCH.
Joining text: &, CONCAT and TEXTJOIN
The & operator glues values together. Fixed text such as a space, a dot or a domain goes in quotes: =A2&" "&B2 gives "Anna Weber". CONCAT is the function version and also accepts whole ranges: =CONCAT(A2," ",B2). The older CONCATENATE still works but cannot take a range.
TEXTJOIN adds a delimiter between all values and can skip empty cells. =TEXTJOIN(", ",TRUE,A2:A7) turns a column into a list like "Anna, Tom, Lisa".
Practice: build email addresses
A classic task when new people join. Make sure the finished address is all lowercase.
Task In C2, build the email address as firstname.lastname@company.com, all in lowercase. The formula is filled down to C7.
| A | B | C | |
|---|---|---|---|
| 1 | First name | Last name | |
| 2 | Anna | Weber | |
| 3 | Tom | Becker | |
| 4 | Lisa | Wagner | |
| 5 | Max | Hoffmann | |
| 6 | Sara | Klein | |
| 7 | Jonas | Fischer |
No formula needed: Text to Columns and Flash Fill
For a one-off cleanup you can skip formulas entirely. Data > Text to Columns splits one column into several at a delimiter such as a space, comma or dash.
Flash Fill is even quicker. Type one or two examples of the result next to your data, for instance the first name from row 2, then press Ctrl+E. Excel spots the pattern and fills the rest of the column. Keep in mind that both tools write static values. When the source data changes, nothing updates, which is where formulas win.
Common mistakes with text functions
The result looks like a number but is text
=RIGHT(A2,4) returns "1042" as text, so sums and comparisons can go wrong. Convert it by multiplying by 1, =RIGHT(A2,4)*1, or with =VALUE(RIGHT(A2,4)).
#VALUE! from FIND
If the character you are looking for is missing, FIND returns #VALUE!. A single name without a space is enough to trigger it. Catch that case with IFERROR: =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2).
Invisible spaces
To Excel, "Boston" and "Boston " are different values. Trailing spaces are one of the top reasons for #N/A in VLOOKUP. TRIM removes them. Data pasted from websites can contain non-breaking spaces that TRIM leaves alone, so swap them first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Text functions in German Excel
German Excel translates the names and separates arguments with semicolons, so =LEFT(A2,3) becomes =LINKS(A2;3).
| English | German |
|---|---|
| LEFT, RIGHT, MID | LINKS, RECHTS, TEIL |
| LEN, FIND, SEARCH | LÄNGE, FINDEN, SUCHEN |
| TRIM, SUBSTITUTE | GLÄTTEN, WECHSELN |
| UPPER, LOWER, PROPER | GROSS, KLEIN, GROSS2 |
| CONCATENATE, CONCAT, TEXTJOIN | VERKETTEN, TEXTKETTE, TEXTVERKETTEN |
| TEXTSPLIT, TEXTBEFORE, TEXTAFTER | TEXTTEILEN, TEXTVOR, TEXTNACH |
Which Excel versions have these functions?
LEFT, RIGHT, MID, LEN, FIND, SEARCH, TRIM, UPPER, LOWER, PROPER, SUBSTITUTE and CONCATENATE work in every version. CONCAT and TEXTJOIN arrived with Excel 2019. Microsoft 365 and Excel 2024 add TEXTSPLIT, TEXTBEFORE and TEXTAFTER: =TEXTSPLIT(A2," ") spreads a full name across two cells, and =TEXTAFTER(A2,"-") returns everything after the dash. If you have them, they replace most MID and FIND combinations.
FAQ
How do I split first and last name in Excel?
Use FIND with LEFT and MID as shown above, Data > Text to Columns for a one-time split, or Flash Fill with Ctrl+E. In Microsoft 365 and Excel 2024, TEXTSPLIT is the shortest option.
How do I add a line break inside a formula?
Use CHAR(10), for example =A2&CHAR(10)&B2. The break only shows when Wrap Text is turned on for the cell.
Why does my date turn into a number when I join it with text?
The & operator drops number formatting. Use TEXT to set the format yourself: ="Due "&TEXT(B2,"mm/dd/yyyy").