Discovering Multiple Occurrences of Text in Excel. This article delves into locating multiple occurrences of text in Excel, specifically addressing scenarios where the standard FIND or SEARCH functions fall short.
Discovering Multiple Occurrences of Text in Excel
Excel Formula:
=SEARCH("~", SUBSTITUTE(string, character, "~", occurrence))
- “~”: A special character ensuring it won’t naturally occur in the string.
- String: The target string for character occurrence search.
- Character: The character whose occurrences are sought.
- Occurrence: The desired instance number of the character in the string.
Example:
Consider strings in A2:A4, characters in C2:C4, and occurrences in D2:D4. To retrieve positions of these occurrences, use the formula in cell E2 and drag down:
=SEARCH("~", SUBSTITUTE(A2, C2, "~", D2))
This formula reveals the position (e.g., 19) of the specified occurrence (e.g., 4) of a character (e.g., space) in the string.
How it Works:
Utilizing the SUBSTITUTE function, which replaces specified occurrences of text in a string, forms the foundation:
SUBSTITUTE(A2, C2, "~", D2)
This resolves to SUBSTITUTE("My name is Anthony Gonsalvis.", " ", "~", 4), resulting in the string “My name is AnthonyGonsalvis.” The chosen character (space) is substituted with “.”
The subsequent SEARCH function, operating on the modified string, becomes SEARCH("~", "My name is Anthony~Gonsalvis."). With “~” occurring only once at position 19, the final result is 19.
Applying similar logic, other formulas are resolved. For case-sensitive searches, substitute SEARCH with FIND:
=FIND("~", SUBSTITUTE(A2, C2, "~", D2))
This article aims to clarify the process of finding text occurrences in Excel and addresses related queries.
