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
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])=Ö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
| Argument | Required | Description |
|---|---|---|
text | Required | The text, or cell, to search. |
delimiter | Required | The delimiter to cut at; one or more characters ("-", " - ", "@"). |
instance_num | Optional | Which occurrence to use; 1 if omitted. Negative numbers count from the end: -1 is the last delimiter. It cannot be 0. |
match_mode | Optional | 0 (default): case-sensitive. 1: case is ignored, so "x" and "X" match. |
match_end | Optional | 0 (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_found | Optional | The 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.
=TEXTBEFORE(A2,"-")| A | B | C | |
|---|---|---|---|
| 1 | Product Code | Group | Code without Colour |
| 2 | KBL-1525-SY | KBL | KBL-1525 |
| 3 | PRZ-21-BY | PRZ | PRZ-21 |
| 4 | SGT-0016-C | SGT | SGT-0016 |
| 5 | KNL-120-GR | KNL | KNL-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.
=TEXTBEFORE(A2," - ",1,0,0,A2)| A | B | |
|---|---|---|
| 1 | Description | Product Name |
| 2 | Grounded socket - White | Grounded socket |
| 3 | Circuit breaker | Circuit breaker |
| 4 | Cable duct - Grey | Cable duct |
| 5 | Light switch - Anthracite | Light 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.
=--TEXTBEFORE(B2," x ",1,1)| A | B | C | |
|---|---|---|---|
| 1 | Product | Size | Width (cm) |
| 2 | Desk | 120 x 60 cm | 120 |
| 3 | Shelf unit | 80 X 40 cm | 80 |
| 4 | Filing cabinet | 45 x 62 cm | 45 |
| 5 | Meeting table | 240 X 110 cm | 240 |
- 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
Put the part of the description before " - " into a Product Name column, or the whole description if there is no dash
Convert to tableBring 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