Sometimes, you may need to find the position or occurrence of a specific character or substring in a text string. For example, you may want to extract the domain name from an email address, or the country code from a phone number. Excel provides several functions and features that can help you perform this task easily and efficiently. In this article, we will explain the basic theory and the procedures of finding a particular character in a text string in Excel formula. We will also provide a comprehensive explanation with examples and screenshots. Finally, we will discuss some alternative approaches that you can use for different scenarios.
The basic theory of finding a particular character in a text string in Excel formula is to use a function that can search for a substring within a larger text and return its position as a number. If the substring is not found, the function will return an error value. There are two main functions that can do this in Excel: the FIND function and the SEARCH function. Both functions have the same syntax:
=FIND(find_text, within_text, [start_num]) =SEARCH(find_text, within_text, [start_num])
The arguments are:
- find_text: the text that you want to find.
- within_text: the text where you want to search for the find_text.
- [start_num]: an optional argument that specifies the starting position of the search. The default value is 1, which means the search will start from the beginning of the within_text.
The difference between the FIND and the SEARCH function is that the FIND function is case-sensitive, while the SEARCH function is not. This means that the FIND function will only match the find_text if it has the exact same case as in the within_text, while the SEARCH function will ignore the case difference. For example:
=FIND(“a”, “Apple”) // returns #VALUE! error =SEARCH(“a”, “Apple”) // returns 1
Another difference is that the SEARCH function supports the use of wildcards, while the FIND function does not. Wildcards are special characters that can match any character or a group of characters. The two main wildcards in Excel are:
- The question mark (?): matches any single character.
- The asterisk (*): matches any sequence of characters, including zero characters.
For example:
=SEARCH(“a?c”, “abc”) // returns 1 =SEARCH(“a?c”, “axc”) // returns 1 =SEARCH(“a?c”, “ayc”) // returns 1 =SEARCH(“ac”, “abc”) // returns 1 =SEARCH(“ac”, “axc”) // returns 1 =SEARCH(“a*c”, “axyzc”) // returns 1
Note that the FIND function will treat the wildcards as literal characters, and will not match them with any other characters. For example:
=FIND(“a?c”, “abc”) // returns #VALUE! error =FIND(“a*c”, “abc”) // returns #VALUE! error
Procedures
To find a particular character in a text string in Excel formula, you can follow these steps:
- Decide which function to use: FIND or SEARCH. If you need a case-sensitive search, or if you do not need to use wildcards, use the FIND function. Otherwise, use the SEARCH function.
- Enter the find_text argument in the first parameter of the function. This is the character or substring that you want to find. You can enter it directly as a text value, or refer to a cell that contains the text. If you use the SEARCH function and you want to use wildcards, make sure to include them in the find_text argument.
- Enter the within_text argument in the second parameter of the function. This is the text where you want to search for the find_text. You can enter it directly as a text value, or refer to a cell that contains the text.
- Optionally, enter the [start_num] argument in the third parameter of the function. This is the starting position of the search. You can enter it as a positive integer, or refer to a cell that contains the number. If you omit this argument, the function will use the default value of 1, which means the search will start from the beginning of the within_text.
- Press Enter to complete the formula. The function will return the position of the first occurrence of the find_text in the within_text as a number. If the find_text is not found, the function will return a #VALUE! error.
Explanation
To illustrate the procedures of finding a particular character in a text string in Excel formula, we will use the following example data:
| Email Address | Phone Number |
|---|---|
| john.doe@gmail.com | +1-202-555-1234 |
| jane.smith@yahoo.com | +44-20-7123-4567 |
| tom.brown@outlook.com | +61-2-9876-5432 |
Suppose we want to find the position of the “@” character in the email address column, and the position of the “-” character in the phone number column. We can use the following formulas:
| Email Address | Phone Number | Formula for “@” | Result for “@” | Formula for “-” | Result for “-” |
|---|---|---|---|---|---|
| john.doe@gmail.com | +1-202-555-1234 | =FIND(“@”,A2) | 9 | =SEARCH(“-”,B2) | 3 |
| jane.smith@yahoo.com | +44-20-7123-4567 | =FIND(“@”,A3) | 11 | =SEARCH(“-”,B3) | 4 |
| tom.brown@outlook.com | +61-2-9876-5432 | =FIND(“@”,A4) | 10 | =SEARCH(“-”,B4) | 4 |
As you can see, we use the FIND function for the “@” character, because it is case-sensitive and does not need wildcards. We use the SEARCH function for the “-” character, because it is not case-sensitive and can use wildcards. We enter the find_text argument as a text value, and the within_text argument as a cell reference. We omit the [start_num] argument, because we want to start the search from the beginning of the text. The formulas return the position of the first occurrence of the find_text in the within_text as a number. If the find_text is not found, the formulas return a #VALUE! error.
