How to Use TEXTBEFORE and TEXTAFTER in Excel (Simple Guide)
- Stefanie Carroll
- Apr 29
- 4 min read
Introduction: Why You Need TEXTBEFORE and TEXTAFTER in Excel
Excel users often deal with messy lists like emails, file names, SKUs, or customer records. Extracting specific parts used to require complicated combinations of FIND, LEFT, RIGHT, and LEN functions. Thankfully, Excel introduced two incredibly useful functions:
TEXTBEFORE and TEXTAFTER. These simplify text extraction, helping you work faster and more efficiently.
What Are TEXTBEFORE and TEXTAFTER Functions?
Understanding TEXTBEFORE
The TEXTBEFORE function returns all text in a cell before a specified character, word, or symbol. It makes isolating the start of a string effortless.
Understanding TEXTAFTER
The TEXTAFTER function extracts everything after your specified delimiter, allowing you to quickly grab trailing information from any text string.
Formula Syntax for TEXTBEFORE and TEXTAFTER
TEXTBEFORE Formula Breakdown
Syntax:
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
TEXTAFTER Formula Breakdown
Syntax:
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Both formulas share identical arguments, designed to give you full control over how and where to split your text.
Explanation of Each Formula Parameter
text
The text you want to split. This could be a cell reference like A2 or a manually typed string.
delimiter
The character, word, or symbol where Excel will split the text.
instance_num
(Optional) Choose which occurrence of the delimiter to split at. Default is 1 (first instance).
match_mode
(Optional) Controls case sensitivity:
0 = Case-sensitive
1 = Case-insensitive (default)
match_end
(Optional) Defines behavior when the delimiter is missing:
0 = Return N/A if delimiter not found (default)
1 = Treat the end of the text as the delimiter and return full text
if_not_found
(Optional) Specifies what to return if the delimiter isn't found, such as a blank, an error, or a custom message.
Simple Example: Pulling Text Before and After a Character
Let’s see these functions in action with an email address example.
Cell A2 contains:
johnsmith@company.ca
To extract the username:
=TEXTBEFORE(A2, "@")
Result: johnsmith
To extract the domain:
=TEXTAFTER(A2, "@")
Result: company.ca
Simple, clean, and powerful!
Advanced Techniques: Using Instance Number and Match Mode
How to Use Instance Number Effectively
Suppose A2 contains:
filefolder_filename_version2
If you want to extract the portion after the second underscore:
=TEXTAFTER(A2, "_", 2)
Result: version2
The instance number allows you to control which delimiter appearance Excel uses for the split.
Case-Sensitive Matching with Match Mode
Imagine you have:
abc-widget
ABC-Widget
You only want to match "ABC-" (uppercase). Using match_mode=0 ensures Excel respects case sensitivity, helping you separate different categories accurately.
Handling Missing Delimiters with match_end
Normally, when the delimiter doesn't exist, Excel returns an error (#N/A) by default (assumes 0). Setting match_end=1 tells Excel to treat the end of the string as the delimiter.
Example:
Assume cell A2 contains "Order123-Ready"
=TEXTBEFORE(A2, "-",,,0) results in "Order123".
=TEXTBEFORE(A2, "-",,,0) returns [#N/A]
=TEXTBEFORE(A2, "-",,,1) returns "Order123Ready".
This option makes your formulas more robust against inconsistent data.
Pro Tip: Using if_not_found to Handle Errors Gracefully
Instead of relying on match_end, a cleaner approach uses the if_not_found argument. Personally, I prefer this method.
Example:
=TEXTBEFORE(A2, "-", , , , A2)
✅ If no dash is found, Excel simply returns the original text instead of an error. This makes your spreadsheets more reliable and user-friendly!
Combo Demo Explanation (Easy Step-by-Step)
In Excel, you can use TEXTBEFORE and TEXTAFTER in combination for extra-slick text handling.
Example 1: Pulling the Invoice Number
Original Text: INV-2024-12345-Paid
Goal: Extract the invoice number only (12345)
Formula used: =TEXTBEFORE(TEXTAFTER(A2,"-",2),"-")
Step-by-Step Breakdown:
First Use TEXTAFTER:
TEXTAFTER(A2, "-", 2)
This tells Excel:➔ Find the second dash - and pull everything after it.
From INV-2024-12345-Paid, after the second -, you get:➔ 12345-Paid
Then Use TEXTBEFORE:
TEXTBEFORE(..., "-")
Now we take that result (12345-Paid) and grab everything before the next dash -.
So from 12345-Paid, we get just:➔ 12345
✅ Final Result: 12345
Example 2: Pulling Client and Service Information
Original Text: Client: Johnson, Service: Plumbing
Goal: Extract the client name (Johnson) and the service type (Plumbing) separately.
Formula Used: =TEXTAFTER(TEXTBEFORE(A2,", "),"Client: ")
Step-by-Step Breakdown
First Use TEXTBEFORE:
TEXTBEFORE(A4, ", ")
This cuts the text before the comma and space (, ).
From Client: Johnson, Service: Plumbing, this gives:➔ Client: Johnson
Then Use TEXTAFTER:
TEXTAFTER(..., "Client: ")
Now we pull the text after the words "Client: ".
From Client: Johnson, we extract:➔ Johnson
✅ Final Result: Johnson Real-World Business Use Cases for TEXTBEFORE and TEXTAFTER
TEXTBEFORE and TEXTAFTER can save massive amounts of time across various industries and businesses. Practical applications include:
Extracting invoice numbers from exported file names
Separating client names or IDs from system-generated reports
Splitting product codes or part numbers from descriptions
Isolating batch numbers or revision IDs from manufacturing records
Cleaning up addresses or service types from project exports
Extracting document versions (draft, final, revision)
Pulling donation details from merged nonprofit data fields
If you work with exported data, messy spreadsheets, or mixed fields, TEXTBEFORE and TEXTAFTER can dramatically accelerate your cleanup process.
Ready to Save Even More Time?
TEXTBEFORE and TEXTAFTER are just the beginning. If messy spreadsheets are slowing you down, automation and smart formulas can revolutionize how you manage your data.
⬇️ Need help streamlining your workflows?
Comments