The SEARCH function in Excel finds text inside a longer string. It returns the character's position. This function is helpful for file tasks.
- You can find a backslash
( \ )in a path - or a dash
( - )in a phone number. - It also finds a space
( )between first and last names.
How to write the SEARCH function
The SEARCH function has three parts: SEARCH(find_text, within_text, [start_num])
- Find_text: The text you want to find. For example, to find a backslash, enter "\".
- Within_text: The text where you search. For example, "C:\Users\Documents\File.xlsx".
- Start_num (optional): Where Excel starts looking. If blank, it starts at the beginning.
If find_text is not found, the SEARCH function returns #VALUE! To handle this error, use IFERROR.
For example, =IFERROR(SEARCH("\", "C:\Users\Documents\File.xlsx"), "Not Found") returns "Not Found" instead of #VALUE! 😉
Example to use the SEARCH function
Here is how to use SEARCH to find the first backslash in a path file. This example returns the position of the first ( \ ) in the path. The result is 3 (the third position in the string).
To find more backslashes, set a start position:
=SEARCH("\", "C:\Users\Documents\MyFile.xlsx", SEARCH("\", "C:\Users\Documents\MyFile.xlsx") + 1)
This example finds the next \ in the file path. It helps separate folders in complex paths. Remember, SEARCH is not case-sensitive. This makes it good for file paths.
New Excel functions to extract on a delimiter
Finding the last backslash ( \ ) using many SEARCH functions can be hard ☹️🫤
Luckily, Excel’s TEXTAFTER and TEXTBEFORE functions simplify this. They make finding parts of file paths easier.
