Text functions in Excel: LEFT, RIGHT, MID, TEXTBEFORE and TEXTJOIN

Many lists arrive with information mixed together in a single cell: a product code that includes a country and a year, a full name, an email address where you only care about the domain. Copying each part by hand is slow and error-prone. Excel’s text functions do the job in seconds and update automatically when the data changes.

Here you will see how to extract characters with LEFT, RIGHT and MID, how to split at a separator with TEXTBEFORE and TEXTAFTER, how to join text with TEXTJOIN and how to avoid the most common mistakes.

LEFT and RIGHT: extracting characters from the ends

These two functions take a fixed number of characters from the start or the end of a text. They work very well with fixed-length codes. Imagine this column of codes in A:

To get the country, take the first two characters:

=LEFT(A2,2)

And for the sequence number, the last four:

=RIGHT(A2,4)

The result of RIGHT is text: “0042”, not 42. If you need to add it up, sort it as a number or compare it with another numeric column, convert it with VALUE:

=VALUE(RIGHT(A2,4))

MID: taking text from the middle

MID returns part of a text starting at a given position. Its syntax is MID(text,start_num,num_chars). In the codes above, the year starts at character 4 and is 4 characters long:

=MID(A2,4,4)

When the position changes from row to row

With full names, the length varies. The solution is to locate the space with SEARCH, which returns the position of a character. If A2 contains “Ana Lopez”, the first name is everything before the space:

=LEFT(A2,SEARCH(" ",A2)-1)

And the surname is everything after it. The third argument is LEN(A2), the total length, to make sure you reach the end:

=MID(A2,SEARCH(" ",A2)+1,LEN(A2))

If a cell has no space, SEARCH returns #VALUE!. You will find this below in the common mistakes.

TEXTBEFORE and TEXTAFTER: splitting at a separator

In Microsoft 365, Excel for the web and Excel 2024 there is a much simpler way to do the above. TEXTBEFORE returns what comes before a delimiter and TEXTAFTER returns what comes after it. You do not need to calculate positions.

For the first name in “Ana Lopez”:

=TEXTBEFORE(A2," ")

For the domain of an email address such as “client.a@example.com”:

=TEXTAFTER(A2,"@")

Choosing which occurrence of the separator to use

The third argument tells Excel at which occurrence of the delimiter to split. With “ES-2025-0042” in A2, this formula returns “ES-2025”, because it splits at the second hyphen:

=TEXTBEFORE(A2,"-",2)

A negative number counts from the end. This way you always get the last block, however many parts the code has:

=TEXTAFTER(A2,"-",-1)

If the separator is not found, both functions return #N/A. You can wrap them in IFERROR to show the original text instead:

=IFERROR(TEXTBEFORE(A2," "),A2)

TEXTJOIN: joining text with a separator

The opposite task is just as common: combining several columns into one, for example to build an address or a list. You can do it with the & operator, but you have to type every separator by hand and empty cells leave stray commas.

TEXTJOIN, available since Excel 2019, solves both problems. Its syntax is TEXTJOIN(delimiter,ignore_empty,text1,...):

=TEXTJOIN(", ",TRUE,A2:C2)

With TRUE as the second argument, the empty cell in the second row does not leave a double comma. It also lets you summarise a whole column in a single cell, for example all the customers on an order:

=TEXTJOIN(", ",TRUE,A2:A20)

If you use Excel 2016 or an earlier version, you do not have TEXTJOIN. You will have to join with & and type the separator in quotation marks:

=A2&", "&C2

To avoid the comma when a cell is empty you would need an IF for each column, which is why the newer function saves so much work.

Practical example: preparing an imported list

Suppose you export a sheet from a program with a “Customer” column that holds the name and email together, such as “Customer A – client.a@example.com”, plus some empty rows in between. To get it ready:

1. Remove extra spaces with =TRIM(A2) in a helper column.
2. Split the name with =TEXTBEFORE(B2," - ") and the email with =TEXTAFTER(B2," - "), where B2 is the cleaned cell.
3. Copy the new columns and paste them with Paste Special › Values so they no longer depend on the formulas.
4. Delete the helper columns and check that no rows without data remain.

Empty rows break filters, PivotTables and the ranges Excel detects automatically. If the file is large, our free tool to remove empty rows deletes them all at once without having to hunt for them. And if the list contains repeated records, you can then run it through the tool to remove duplicates.

Common mistakes and how to avoid them

• Numbers that will not add up: LEFT, RIGHT and MID always return text. Use VALUE or multiply by 1 if you need a number.
• Invisible spaces: a leading space shifts every position. Apply TRIM before extracting.
• #VALUE! with SEARCH: it appears when the character you are looking for is not in the cell. Check those rows or use IFERROR.
• #NAME? with TEXTBEFORE or TEXTJOIN: your version of Excel does not include the function. Use the combination of LEFT, MID and SEARCH, or the & operator.
• Argument separator: English versions of Excel use commas; if you copy a formula from a regional version that uses semicolons, change them.

Conclusion

Five functions cover almost any text task: LEFT and RIGHT for fixed-length ends, MID with SEARCH for variable positions, TEXTBEFORE and TEXTAFTER for splitting at a separator in recent versions, and TEXTJOIN for joining. Clean up spaces before you start and convert to values when you finish.

Frequently asked questions