Master Text Analysis with LEN, FIND, and SEARCH (Positions, Counts, and Nth Extraction)
Extract codes from SKUs, separate domains from email addresses, and find keyword positions, counts, and nth occurrences—use LEN/FIND/SEARCH for accurate and efficient results.
Syntax and Differences
| Function | Syntax | Key Point |
|---|---|---|
| LEN | =LEN(text) | Number of characters, including spaces |
| FIND | =FIND(find_text, within_text, [start_num]) | Case-sensitive; no wildcards |
| SEARCH | =SEARCH(find_text, within_text, [start_num]) | Not case-sensitive; supports * and ? wildcards |
Returns: A position starting at 1. If no match is found, returns #VALUE!. Use start_num to change the starting position.
Find Positions (First, Last, and Nth)
First Position (Case-Insensitive)
=SEARCH("@" , A2)Last Position (Search from the End)
=FIND("§", SUBSTITUTE(A2, "/", "§", LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))))Position of the Nth Occurrence
=FIND("§", SUBSTITUTE(A2, "keyword", "§", N))If the keyword does not exist, the formula returns #VALUE!—handle it with IFERROR.
Extract Text Before, After, and Between
Text Before a Delimiter
=LEFT(A2, FIND("-", A2)-1)Text After a Delimiter
=RIGHT(A2, LEN(A2)-FIND("-", A2))Text Between Two Characters (MID)
=MID(A2, FIND("[",A2)+1, FIND("]",A2)-FIND("[",A2)-1)365 For a simpler approach, use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT.
Count Occurrences
Count a Single Keyword
=LEN(A2)-LEN(SUBSTITUTE(A2, "keyword", ""))Case-Sensitive Count (Advanced)
=SUM(--ISNUMBER(FIND("Pro", MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 3))))For long text, SCAN/MAP in Microsoft 365 or Power Query is more efficient.
8 Practical Patterns
① Extract the Domain from an Email Address
=RIGHT(A2, LEN(A2)-FIND("@",A2))② Extract the File Name from a File Path (After the Last )
=RIGHT(A2, LEN(A2)-FIND("§",SUBSTITUTE(A2,"","§",LEN(A2)-LEN(SUBSTITUTE(A2,"","")))))③ Extract the Middle Block from SKU-123-456
=MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)④ Extract the Nth Word (Space Delimiter)
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)), (N-1)*99+1, 99))⑤ Return TRUE If a Keyword Exists (Case-Sensitive)
=ISNUMBER(FIND("Pro", A2))⑥ Wildcard Pattern Matching (Case-Insensitive)
=ISNUMBER(SEARCH("A??-2025*", A2))⑦ Separate a Code by Prefix Length
=RIGHT(A2, LEN(A2)-LEN("PRD-")) // Remove the fixed "PRD-" prefix⑧ Extract N Characters to the Left or Right of a Keyword
=LEFT(A2, FIND("KEY",A2)-1)
=RIGHT(A2, LEN(A2)-FIND("KEY",A2)-LEN("KEY")+1)Common Mistakes and Checklist
- #VALUE! → Occurs when the search value is not found. Use
IFERROR(formula, "")to handle it safely. - Multibyte characters → LEN counts characters, while
LENBcounts bytes (Korean characters use 2 bytes). Be careful when working with file systems. - Spaces → Clean hidden spaces with
TRIM/CLEAN/SUBSTITUTE(,CHAR(160),)before comparing. - Case rules → Use FIND for exact matches and SEARCH for flexible searches or patterns.
- Complex MID chains → In Microsoft 365, simplify them with
TEXTBEFORE/AFTER/SPLIT.
Summary
| Goal | Typical Formula |
|---|---|
| Length | LEN(text) |
| First, last, or nth position | SEARCH/ FIND + SUBSTITUTE |
| Extract before, after, or between text | LEFT/RIGHT/MID (or TEXTBEFORE/AFTER) |
| Occurrence count | LEN- LEN(SUBSTITUTE(...)) |
FAQ
Can I find which of several keywords appears first?
Use MIN(IFERROR(SEARCH({"A","B","C"},A2),1E9)) to select the smallest position, then use INDEX to return the matching key (array formula).
What if I need the byte length of text containing both Korean and English characters?
Use LENB (results may vary by system and encoding).