Excel FIND Function: A Practical Guide from the Basics to Nth Delimiters and Text Extraction

The Excel FIND function finds the starting position of a specific text string within text. This guide covers practical, step-by-step patterns—from basic syntax to extracting text before, after, and between delimiters; handling the Nth delimiter; managing errors and nonbreaking spaces; and using the Microsoft 365 TEXTAFTER/TEXTBEFORE alternatives.

Quick Fix

1) Basic Syntax and Essential Patterns

Syntax: =FIND(find_text, within_text, [start_num]) — case-sensitive and does not support wildcards.
Find a basic position: =FIND("@", A2) → The starting position of "@" in A2.
Extract before a delimiter (left): =LEFT(A2, FIND("@",A2)-1)
Extract after a delimiter (right): =MID(A2, FIND("@",A2)+1, LEN(A2))
Extract between two symbols (for example, inside [ ]):
=MID(A2, FIND("[",A2)+1, FIND("]",A2)-FIND("[",A2)-1)

2) Nth Delimiter Position and Extraction

Use SUBSTITUTE to find the Nth delimiter, such as the last period or hyphen.

Position of the Nth delimiter:
=FIND("#", SUBSTITUTE(A2, "-", "#", N))
Everything after the Nth delimiter:
=MID(A2, FIND("#",SUBSTITUTE(A2,"-","#",N))+1, LEN(A2))
Between the Nth and N+1th delimiters:
=MID(A2, FIND("#",SUBSTITUTE(A2,"-","#",N))+1, FIND("#",SUBSTITUTE(A2,"-","#",N+1)) - FIND("#",SUBSTITUTE(A2,"-","#",N)) - 1)

In Microsoft 365, this is simpler: =TEXTAFTER(A2, "-", N), =TEXTBEFORE(A2, "-", N).

3) Error and Exception Handling

Text not found: FIND returns #VALUE! → Use =IFERROR(formula,"") or a message such as "Not found".
Starting position: If start_num is less than 1 or exceeds the text length, Excel returns #VALUE!.
Nonbreaking spaces and hidden characters: Clean the text with TRIM, CLEAN, and SUBSTITUTE(,CHAR(160)," ") before using FIND.

Why These Issues Occur: Concepts and Principles

FIND vs SEARCH

FIND: Case-sensitive and has no wildcard support. Best for exact pattern searches.
SEARCH: Case-insensitive and supports the ? and * wildcards. Useful for flexible searches and pattern matching.
If you want to ignore case, use SEARCH or an uppercase conversion technique such as FIND(UPPER(find), UPPER(within)).

Starting Position and Length Calculations

Text extraction ultimately comes down to a starting point and a length. Use FIND/SEARCH for the starting point, and calculate length as next boundary - start - 1 or LEN(text) - start.

Handling Nonbreaking Spaces and Hidden Characters

Data copied from the web or an ERP system may contain nonbreaking spaces (CHAR(160)), tabs (9), and line breaks (10/13). Normalize the text before applying FIND for more reliable results.

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Practical Examples

Sample Data

Row	A (Original Text)	Purpose
2	kim@exceljump.com	Split username and domain
3	Report_v2.1_final.xlsx	Extract the extension and version
4	[KR] 2025-11-15 / CENTER-02	Parse based on brackets and slashes
5	P1001-SEOUL-XL	Break down SKU details
6	Tel: 010 1234 5678	Extract the number sequence

1) Email: Username and Domain

Username: =LEFT(A2, FIND("@",A2)-1) → kim
Domain: =MID(A2, FIND("@",A2)+1, LEN(A2)) → exceljump.com
TLD: =RIGHT(A2, LEN(A2)-FIND(".", A2, FIND("@",A2))) → com

2) File Name: Extension Based on the Last Period

Use the SUBSTITUTE technique to identify the position of the last period.

=RIGHT(A3, LEN(A3) - FIND("#", SUBSTITUTE(A3, ".", "#", LEN(A3)-LEN(SUBSTITUTE(A3,".","")))))

In Microsoft 365, use =TEXTAFTER(A3,".",-1) for a simpler formula.

3) Between a Pair of Symbols (Brackets and Slashes)

Country code (brackets): =MID(A4, FIND("[",A4)+1, FIND("]",A4)-FIND("[",A4)-1) → KR
Center code (after the slash and space): =MID(A4, FIND("/",A4)+2, LEN(A4)) → CENTER-02

4) Break Down SKU Details (Nth Hyphen)

A5 = P1001-SEOUL-XL

Product code (before the first hyphen): =LEFT(A5, FIND("-",A5)-1) → P1001
City (between the first and second hyphens):
=MID(A5, FIND("-",A5)+1, FIND("#",SUBSTITUTE(A5,"-","#",2)) - FIND("-",A5) - 1) → SEOUL
Size (after the second hyphen):
=MID(A5, FIND("#",SUBSTITUTE(A5,"-","#",2))+1, LEN(A5)) → XL

In Microsoft 365: =TEXTBEFORE(A5,"-"), =TEXTAFTER(TEXTBEFORE(A5,"-",2),"-"), =TEXTAFTER(A5,"-",2).

5) Extract a Number Sequence (Mixed Text and Numbers)

A6 = Tel: 010 1234 5678

Starting position of the first number: =MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9}&"", A6), "")) (array formula)
Phone number only: =TEXTJOIN("",,IFERROR(MID(A6,SEQUENCE(LEN(A6)),1)*1,"")) → 01012345678

6) Handle Multiple Delimiters (TEXTSPLIT)

If various delimiters such as commas, semicolons, and spaces are mixed together:

=TEXTSPLIT(A2, {",",";"," "})

After splitting, use INDEX/TAKE/DROP to return only the pieces you need, or use TEXTJOIN to join them again.

7) Tables (Structured References) and Dynamic Arrays

Assume you convert the range to a table (Ctrl+T) named tblEmail. To return only usernames from the [Email] column:

=LEFT(tblEmail[Email], FIND("@", tblEmail[Email]) - 1)

The formula automatically expands as data grows, and in Microsoft 365 the results spill, making it useful for column-level processing.

8) Improve Readability and Performance with LET

=LET(
  s, A5,
  p1, FIND("-", s),
  p2, FIND("#", SUBSTITUTE(s,"-","#",2)),
  code, LEFT(s, p1-1),
  city, MID(s, p1+1, p2-p1-1),
  size, MID(s, p2+1, LEN(s)),
  code & " | " & city & " | " & size
)

Alternatives, Notes, and Checklist

Need case-insensitive matching or wildcards ⇒ Use SEARCH or TEXTAFTER/TEXTBEFORE (Microsoft 365).
Last/Nth delimiter ⇒ Use the SUBSTITUTE technique or TEXTAFTER(...,-1).
Error safety ⇒ Use IFERROR to return a blank or message instead of #VALUE!.
Nonbreaking spaces ⇒ Preprocess with TRIM/CLEAN/SUBSTITUTE(,CHAR(160)," ").
Converting data to a table improves maintainability and scalability, while LET minimizes repeated calculations.

Troubleshooting

Symptom	Cause	Solution
#VALUE! error	The target text is missing, or start_num is out of range	Use IFERROR, reset start_num to 1, and preprocess with TRIM/CLEAN
Text is not found because of letter case	FIND is case-sensitive	Use SEARCH or normalize with UPPER/LOWER
Hard to calculate the last or second delimiter	FIND returns only the first match	Use the SUBSTITUTE technique or TEXTAFTER/TEXTBEFORE
Positions are off with Korean text or spaces	Nonbreaking spaces or hidden characters	Use TRIM/CLEAN after SUBSTITUTE(,CHAR(160),” “)
Complex nesting reduces readability	Repeated FIND/LEN calculations	Use LET variables and structured references

If your report format changes frequently, save these Excel FIND function patterns as templates to reduce time spent on repetitive tasks.

엑셀 중복 문자 치환 가이드: 연속·전체·단어 중복 정리

Excel FIND Function Guide: Nth Delimiter, Text Extraction, and SEARCH Comparison