Skip to content

Excel function · Text

Excel TEXTBEFORE function

TEXTBEFORE returns the part of a text that comes before the delimiter you give it: the product group before a hyphen, the product name before " - ", the width before an "x". It is available in Microsoft 365 and Excel 2024.

In Turkish Excel: ÖNCEKİMETİN

What it does

TEXTBEFORE does in one step what LEFT and FIND do together: it looks for the delimiter and returns everything before it. =TEXTBEFORE("KBL-1525-SY","-") returns "KBL". There is no counting of characters and no subtracting one.

The third argument says which occurrence of the delimiter to use. Negative numbers count from the end: -1 is the last one, so =TEXTBEFORE("KBL-1525-SY","-",-1) returns "KBL-1525". With 1 as the fourth argument the delimiter is matched regardless of case, and the last argument sets the value to return instead of #N/A when the delimiter is missing.

TEXTBEFORE exists only in Microsoft 365, Excel 2024 and Excel for the web; older versions show #NAME?. For the part after the delimiter use TEXTAFTER, and to split the text at every delimiter use TEXTSPLIT.

Syntax

English Excel=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Turkish Excel=ÖNCEKİMETİN(metin; sınırlayıcı; [kaçıncı]; [eşleştirme_modu]; [eşleştirme_sonu]; [bulunamazsa])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
textRequiredThe text, or cell, to search.
delimiterRequiredThe delimiter to cut at; one or more characters ("-", " - ", "@").
instance_numOptionalWhich occurrence to use; 1 if omitted. Negative numbers count from the end: -1 is the last delimiter. It cannot be 0.
match_modeOptional0 (default): case-sensitive. 1: case is ignored, so "x" and "X" match.
match_endOptional0 (default): the end of the text is not a delimiter. 1: the end of the text counts as one, so the whole text is returned when the delimiter is missing.
if_not_foundOptionalThe value to return when the delimiter is not found. If omitted, the result is #N/A.

Examples

1Take the text before the first and the last hyphen

Column B returns the product group before the first hyphen; column C uses -1 to take everything before the last one. The middle part has a different length on each row, and the result is still right.

B2fx=TEXTBEFORE(A2,"-")
Take the text before the first and the last hyphen: example table
ABC
1Product CodeGroupCode without Colour
2KBL-1525-SYKBLKBL-1525
3PRZ-21-BYPRZPRZ-21
4SGT-0016-CSGTSGT-0016
5KNL-120-GRKNLKNL-120
  • B2 (filled down to B5)=TEXTBEFORE(A2,"-")
  • C2 (filled down to C5)=TEXTBEFORE(A2,"-",-1)

The first row returns KBL and KBL-1525; the second returns PRZ and PRZ-21.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Return the whole text when the delimiter is missing

Most descriptions have the colour after " - ", but some have no colour. The last argument is A2, so a row without the delimiter returns the whole description.

B2fx=TEXTBEFORE(A2," - ",1,0,0,A2)
Return the whole text when the delimiter is missing: example table
AB
1DescriptionProduct Name
2Grounded socket - WhiteGrounded socket
3Circuit breakerCircuit breaker
4Cable duct - GreyCable duct
5Light switch - AnthraciteLight switch
  • B2 (filled down to B5)=TEXTBEFORE(A2," - ",1,0,0,A2)

B2 shows Grounded socket, and B3, which has no colour, shows Circuit breaker. Without the last argument, B3 would show #N/A.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Get the width from a size, ignoring case

Sizes are written as "120 x 60 cm", but some use a capital X. With 1 as the fourth argument both are found, and the two minus signs turn the result into a number.

C2fx=--TEXTBEFORE(B2," x ",1,1)
Get the width from a size, ignoring case: example table
ABC
1ProductSizeWidth (cm)
2Desk120 x 60 cm120
3Shelf unit80 X 40 cm80
4Filing cabinet45 x 62 cm45
5Meeting table240 X 110 cm240
  • C2 (filled down to C5)=--TEXTBEFORE(B2," x ",1,1)

The rows with a capital X work too: C3 shows 80 and C5 shows 240. Without the fourth argument, those two rows would return #N/A.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#N/A

Cause: The delimiter is not in the text, or it does not appear as many times as requested (a third hyphen in a code that has two).

Fix: Give the last argument a value (for example the text itself: =TEXTBEFORE(A2," - ",1,0,0,A2)) or set match_end to 1.

#VALUE!

Cause: instance_num is 0 or larger than the length of the text.

Fix: Use 1, 2… or -1, -2… to count from the end.

#NAME?

Cause: The file was opened in an older version of Excel that does not know TEXTBEFORE.

Fix: In older versions, =LEFT(A2,FIND("-",A2)-1) does the same job.

Good to know

  • The result is text. To calculate with digits before the delimiter, put two minus signs in front: =--TEXTBEFORE(B2," x ").
  • To get the part between two delimiters, nest it with TEXTAFTER: =TEXTBEFORE(TEXTAFTER(A2,"("),")").

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Put the part of the description before " - " into a Product Name column, or the whole description if there is no dash

Convert to table

Bring your product list into the tool and type this. Nexell builds the Product Name column with real Excel text formulas and shows it in the preview; in the downloaded file, the name follows the description.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between TEXTBEFORE and LEFT?

LEFT needs to know how many characters to take, so it is paired with FIND to stop at a delimiter. TEXTBEFORE takes the delimiter directly, can find the last occurrence and accepts a value for when nothing is found.

How do I get the text before the last hyphen in Excel?

In Microsoft 365, use =TEXTBEFORE(A2,"-",-1); -1 picks the last delimiter. For "KBL-1525-SY" the result is "KBL-1525".

Related functions

Step-by-step guide: Text to Excel: split text into columns

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free