Returns the position of a specified character or sub-string within a supplied text string
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
The SEARCH Function is categorized under Excel TEXT functions. SEARCH will return the position of a specified character or sub-string within a supplied text string.
In financial analysis, the SEARCH function can be useful if we wish to find the location of a text in a string. For example, we can use the function to find the position of the letter “n” in the word “name.”
=SEARCH( search_text, within_text, [start_num] )
The SEARCH function uses the following arguments:
Search_text (required argument) – This is the character or sub-string we wish to search.
Within_text (required argument) – The text string that we need to search.
Start_num (optional argument) – Specifies the position the within_text string from which the search should begin. If we omit this argument, it will take on the default value of 1, that is, it will begin the search at the start of the within_text string.
How to use the SEARCH Function in Excel?
As a worksheet function, SEARCH can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider an example:
Suppose we imported data from an external source and we wish to find certain characters from the values given below:
The results in Excel are given in the screenshot below:
A few notes about the SEARCH Function:
1. #VALUE! error – Occurs when:
a. The given search_text is not found in the supplied within_text string.
b. The given [start_num] is less than zero or is greater than the length of the supplied within_text string.
2. We can use the following wildcards in the search_text argument:
a. ? – Matches any single character.
b. * – Matches any sequence of characters. For example, the condition “A*e” will match any substring beginning with “A” and ending in “e.”
3. The FIND function doesn’t support case-sensitivity or use of wildcards so we can use the SEARCH function to search without case-sensitivity and/or to use wildcards.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional CFI resources:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.