Text Excel 365 ◉ 11 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 — Coming soon ▦ 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)

📥 Download TEXTBEFORE.xlsx — Coming soon ~ 8–12 KB · No macros

🧠 🧠 Quiz this function

%d questions — syntax, example & errors — test yourself!

Start quiz →

◧ 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!