Text
Excel 365
◉ 18 views
=TEXTBEFORE()
Value returned if no match is found. By default, #N/A is returned.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for Mac
=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
Hover argument names in the table for details.
Output typeText
Popularity55 / 100
VolatileNo
Dynamic ArrayNo
📥 Download sample .xlsx ▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =TEXTBEFORE() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
🧠 🧠 Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
text |
Text | Required | The text you are searching within. Wildcard characters are not allowed. If text is an empty string, Excel returns empty text. |
delimiter |
Text | Required | The text that marks the point before which you want to extract. |
instance_num |
Number | Optional | The instance of the delimiter after which you want to extract the text. By default, instance_num = 1. A negative number starts searching text from the end. |
match_mode |
Number | Optional | Determines whether the text search is case-sensitive. The default is case-sensitive. Enter one of the following: Values: 0 = Case sensitive.; 1 = Case insensitive.. |
match_end |
Text | Optional | Treats the end of text as a delimiter. By default, the text is an exact match. Enter the following: Values: 0 = Don't match the delimiter against the end of the text.; 1 = Match the delimiter against the end of the text.. |
if_not_found |
Number | Optional | Value returned if no match is found. By default, #N/A is returned. |
▣ Examples
▦ Data
| Cell | Value |
|---|---|
A2 |
Little Red Riding Hood's red hood |
A3 |
Data |
A4 |
Little red Riding Hood's red hood |
A5 |
Marcus Aurelius |
A6 |
Socrates |
A7 |
Immanuel Kant |
A8 |
Little Red Riding Hood's red hood |
A9 |
Data |
A10 |
Little red Riding Hood's red hood |
A11 |
Marcus Aurelius |
A12 |
Socrates |
A13 |
Immanuel Kant |
Little
=TEXTBEFORE(A2,"Red")
#N/A
=TEXTBEFORE(A3,"Red")
Little red Riding Hood's
=TEXTBEFORE(A3,"red",2)
Little
=TEXTBEFORE(A3,"red",-2)
#N/A
=TEXTBEFORE(A3,"Red",,FALSE)
#N/A
=TEXTBEFORE(A3,"red",3)
Marcus
=TEXTBEFORE(A2," ",,,1)
#N/A
=TEXTBEFORE(A3," ",,,0)
Socrates
=TEXTBEFORE(A3," ",,,1)
Immanuel
=TEXTBEFORE(A4," ",,,1)
§ Remarks
- When searching with an empty delimiter value, TEXTBEFORE matches immediately. It returns empty text when searching from the front (if instance_num is positive) and the entire text when searching from the end (if instance_num is negative).
✦ Tips
- When searching with an empty delimiter value, TEXTBEFORE matches immediately. It returns empty text when searching from the front (if instance_num is positive) and the entire text when searching from the end (if instance_num is negative).
⚠ Common Errors
#N/A
By default, #N/A is returned. Excel returns a #N/A error if delimiter isn't contained in text. Excel returns a #N/A error if instance_num is greater than the number of occurrences of delimiter in text.
Fix: Check the delimiter or specify a fallback value in the last argument
#VALUE!
Excel returns a #VALUE! error if instance_num = 0 or if instance_num is greater than the length of text.
Fix: برای instance_num مقدار غیرصفر و کوچکتر یا مساوی طول متن وارد کنید.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!