LEFT vs TEXTBEFORE: Which Splits Text Better?

The answer to LEFT vs TEXTBEFORE depends on how the text is built. LEFT takes a fixed number of characters from the start, which is perfect when every code has the same length. TEXTBEFORE takes everything before a delimiter such as a dash, space or @, which is what you need when lengths vary. This comparison is for anyone splitting codes, names or emails who wants the formula that won't break on the next row.

LEFT vs TEXTBEFORE at a glance#

LEFT counts characters. TEXTBEFORE looks for a marker.

How each function works#

LEFT returns the first n characters: =LEFT("NY-1182",2) gives NY. If you leave out num_chars, it returns 1 character. It never looks at what the characters are, which is both its strength and its weakness. The LEFT function guide and LEFT, RIGHT and MID cover it with RIGHT and MID.

TEXTBEFORE finds the delimiter and returns what comes before it: =TEXTBEFORE("LON-4521","-") gives LON. Its optional arguments choose which delimiter to use, whether case matters, and what to return when the delimiter is missing. Its partner, TEXTAFTER, returns the part after the delimiter; TEXTBEFORE and TEXTAFTER covers both.

The same codes, side by side#

Here are product codes whose region prefix is 2 or 3 letters long:

The three formulas, in row 2:

=LEFT(A2,2)
=LEFT(A2,FIND("-",A2)-1)
=TEXTBEFORE(A2,"-")

Plain LEFT is wrong for LON and BRS, because it always takes two characters. It doesn't show an error, so the mistake is easy to miss.

LEFT with FIND gets them right. FIND returns the dash's position (4 in LON-4521), and minus 1 stops just before it. This works in every version of Excel.

TEXTBEFORE gets them right with a shorter, readable formula. For BRS-3004-B it stops at the first dash.

When the delimiter is missing#

Row 6, MAN, has no dash. LEFT with FIND returns #VALUE! because FIND fails. TEXTBEFORE returns #N/A. Each has a fix:

=IFERROR(LEFT(A6,FIND("-",A6)-1),A6)
=TEXTBEFORE(A6,"-",,,,A6)
=TEXTBEFORE(A6,"-",,,1)

All three return MAN. The second uses if_not_found (the sixth argument) to return the whole cell. The third sets match_end to 1, which treats the end of the text as a delimiter.

Splitting at the last delimiter#

For BRS-3004-B, suppose you want everything before the last dash. TEXTBEFORE takes -1 as the instance number:

=TEXTBEFORE(A5,"-",-1)

The result is BRS-3004. With LEFT, you'd need SUBSTITUTE and LEN to find the last dash, which is hard to read and easy to get wrong.

Getting the part after the dash#

The same contrast applies on the other side of the delimiter, where RIGHT meets TEXTAFTER. To get the number from LON-4521:

=RIGHT(A3,LEN(A3)-FIND("-",A3))
=TEXTAFTER(A3,"-")

Both return 4521. The RIGHT version works out how many characters follow the dash: the total length (LEN, 8) minus the dash's position (FIND, 4), which is 4. TEXTAFTER just names the delimiter. For BRS-3004-B, TEXTAFTER with -1 returns B, the part after the last dash.

Remember that both results are text. =VALUE(TEXTAFTER(A3,"-")) turns "4521" into the number 4521, so SUM and comparisons work.

Which version of Excel do you have?#

Before choosing, check your version: in Windows Excel, go to File > Account and look under Product Information; on a Mac, choose Excel > About Excel. If it says Microsoft 365 or Excel 2024, TEXTBEFORE is available. If it says Excel 2021, 2019 or 2016, it isn't, and a TEXTBEFORE formula shows #NAME?.

If you don't know which version a test or a colleague uses, LEFT with FIND is the safe choice. It gives the same result for every row that contains the delimiter.

When to use LEFT#

Use LEFT when the length is fixed or the file must work everywhere. It is the better choice when:

• Every value has the same structure, such as TX-ELC-40512, where the region is always 2 characters.
• You need a set number of characters, such as the first letter of a name for initials: =LEFT(A2,1).
• The workbook must open in Excel 2021 or older. Use LEFT with FIND for variable lengths.
• A job test asks for it. LEFT, RIGHT, MID and FIND are still the classic text questions.

When to use TEXTBEFORE#

Use TEXTBEFORE when a delimiter marks the end of the part you want and everyone has Microsoft 365 or Excel 2024. It is the better choice when:

• Lengths vary, such as first names before a space or usernames before an @.
• You need the 2nd or last delimiter, such as a folder path or a name with a middle name.
• Some rows may not have the delimiter and you want a clean fallback.
• Readability matters. TEXTBEFORE(A2,"@") says exactly what it does.

Splitting full names is the most common real case; see how to split first and last names.

Common mistakes with LEFT and TEXTBEFORE#

1. Using a fixed LEFT on variable data. It silently returns wrong results, like LO instead of LON. Check a few rows with different lengths.
2. Forgetting the -1 with FIND. =LEFT(A2,FIND("-",A2)) includes the dash: NY-.
3. Expecting a number back. Both return text, so =LEFT("2026-Q4",4) gives the text "2026". Wrap it in VALUE to calculate with it.
4. Hidden spaces. A leading space in " NY-1182" shifts LEFT's count. Clean with TRIM first.
5. Case in letter delimiters. TEXTBEFORE is case-sensitive by default, so "X" won't match "x". Use match_mode 1, or SEARCH instead of FIND with LEFT.
6. Sharing TEXTBEFORE with Excel 2021 users. They see #NAME?. Use LEFT with FIND if you're not sure.

If you'd rather not write a formula at all, Flash Fill vs formulas and Text to Columns vs TEXTSPLIT compare the click-based options.

Our recommendation#

Use TEXTBEFORE for new work on Microsoft 365 or Excel 2024 whenever a delimiter marks the split. It is shorter, it handles variable lengths, and its extra arguments solve the "second dash" and "no dash" problems that make LEFT formulas messy.

Keep LEFT for fixed-length parts and for files that must work in older versions, where LEFT(A2,FIND("-",A2)-1) is the reliable pattern. Learn both: job tests often run on unknown versions, and LEFT with FIND works on all of them.

In short: LEFT for fixed lengths and old Excel, TEXTBEFORE for anything that ends at a delimiter.

Practise it#

Split the region code in the exercise on this page. Then practise fixed-length splits in the LEFT, RIGHT, MID and LEN lesson, and delimiters, instance numbers and the LEFT + FIND fallback in the TEXTBEFORE and TEXTAFTER lesson.