Afrina Nafisa
While working on a large dataset sometimes you need to check certain values that begin with specific data. In case you work on a smaller dataset finding those values is quite easy to do manually. But in case you are working on a large dataset then this process is next to impossible. We can find out our required cells which start with a particular data using Excel formulas. In this article, we will learn Excel if cell begins with certain text then return value.
The above overview image shows the overall used formula and its explanation which is shown in this article. This article covered every possible way to execute this issue. You will know better about this process once you go through the total article.
Download Practice Workbook
You can find the practice sheet here.
If Cell Begins with Certain Text Then Return Value in Excel: 3 Examples
The dataset below represents the Outlets of ABC Enterprise with Product Name, and Product ID stored in every outlet. Some products are common in different outlets so the Product ID will also be common. Here, we want to find out Excel if cell begins with certain text then return value using this dataset. Follow these possible ways to execute this process.
1. Excel If Cell Begins with Certain Text Then Return Value: Combination of IF and LEFT Functions
Here we will match starting of cells using the combination of IF and LEFT functions.
The LEFT function extracts a certain number of characters from the very left of the cell. Then, the IF function is used to make logical comparison between the given values and the resulatant value. Follow the below procedure to get the required value using a formula based on these functions.
- Select cell E5 to enter the formula.
=IF(LEFT(D5,3)="VSX",D5,"Not Found")
- Drag the Fill Handle icon.
Formula Breakdown
LEFT(D5,3)
the LEFT function extracts 3 letters or characters from the left or start of cell D5.
Result: VSX
IF(LEFT(D5,3)=”VSX”,D5,”Not Found”)
The IF function compares the result of the LEFT function and returns the value if true or shows Not Found if false.
Result: VSX-123
Note:
If you don’t want to write down the number of data in the formula manually, you can use the LEN function like the below formula. This option is the alternative to make the formula dynamic.
=IF(LEFT(D5,LEN(“VSX”))=”VSX”,D5,”Not Found”)
2. Return Value If Cell Begins with Certain Text Using IF and COUNTIF Functions
Here, we will use the combination of IF and COUNTIF functions to the return value if the cell begins with certain text. Once you follow the steps you will figure out how to execute this process.
- Select cell D5 to apply the formula here.
=IF(COUNTIF(D5,"VSX*"),D5,"Not Applicable")
- Once the formula is complete drag down the Fill Handle to apply the same formula in the total dataset.
Here, Wildcard (*) is used in the end of the required word. That means this word is in the beginning of the string.
Formula Breakdown
COUNTIF(D5,”VSX*”)
This function decides if any text starting with VSX or not in cell D5. If found results 1, otherwise returns 0.
Result: 1
IF(COUNTIF(D5,”VSX*”), D5,”Not Applicable”)
Here, the IF function shows the final output. If the output of the COUNTIF function is 1 then the final output shows as the data of D5 otherwise “Not Applicable”.
Result: VSX-123
3. Combination of IF, IFERROR, and SEARCH Functions to Return Value If Cell Starts with Specific Text
Here the actual output can be established with the combination of IF, IFERROR, and SEARCH functions. Now, follow the below steps to successfully implement this method.
- First, select cell F5 to apply the formula.
=IFERROR(IF(SEARCH(E5,D5,1)=1,E5,0),"")
- Once the formula is complete drag down the fill handle or double-click on the fill handle to execute this formula in the total dataset.
Formula Breakdown
SEARCH(E5,D5,1)
The SEARCH function is used to search similar data from cell D5 to cell E5. 3rd argument indicates search word must locate at the starting of cell D5.
Result: 1
IF(SEARCH(E5,D5,1)=1,E5,0)
Here, the IF function shows the final output. If the output of the SEARCH function fulfills the condition then the final output shows as the data of D5 otherwise “Not Applicable”.
Result: VSX-123
IFERROR(IF(SEARCH(E5,D5,1)=1,E5,0),””)
The IFERROR function is avoid erroe in result. Here,for any error formula will show blank cell otherwise result of the IF function.
Result: VSX-123
Note:
Instead of using the SEARCH function, you can also use the FIND function. The FIND function also works in this case. But the FIND function is case-sensitive. So when we need matching with case-sensitivity, we can use this formula.
=IFERROR(IF(FIND(E5,D5,1)=1,E5,0),"")
If a Cell Ends with Certain Text then Return Value in Excel
Sometimes we need outputs based on the end of the strings. This procedure is simple and quite similar to getting a return value based on the beginning of the string. Instead of using the LEFT function here, we can use the RIGHT function to get the return value.
- In the beginning, select cell E5 to apply the formula.
=IF(RIGHT(C5,5)="Phone",C5,"Not Applicable")
- After applying the formula drag down the fill handle to execute this process.
How to Check If a Cell Contains Certain Text in Excel
Here, we will show how to check if a cell contains a certain text using the combination of IF and COUNTIF functions.
- We will also use the wildcard (*) while applying the formula in cell E5.
=IF(COUNTIF(C5,"*Phone*"),C5,"Not Applicable")
- Once you use the wildcard it will find out that particular text from anywhere in the string.
Here, this function is quite similar to the function shown earlier in method 2.
How to Check If a Cell Starts with a Number in Excel
In this section, we will check if a particular cell starts with a number. When you work in a large dataset sometimes it is difficult to find out if the data starts with numbers. But using the below method we can simply identify the cells which start with a number. The formula of this process contains the combination of the VALUE, ISNUMBER, LEFT, and IF functions.
- We have used the following formula on cell E5 and drag the Fill Handle icon.
=IF(ISNUMBER(VALUE(LEFT(D5,2))), D5,"Not Applicable")
Formula Breakdown
LEFT(D5,3)
LEFT function shows first 3 letters from the beginning as the output.
Result:
VALUE(LEFT(D5,3))
Here, this function shows the value of the 3 characters from the beginning as we used the LEFT function.
Result: 123
ISNUMBER(VALUE(LEFT(D5,3)))
This function identifies if the function matches the conditions. Here, the ISNUMBER function verifies if the output of the VALUE function matches the condition. If the condition matches the output will be TRUE.
Result: True
IF(ISNUMBER(VALUE(LEFT(D5,2))), D5,”Not Applicable”)
Lastly if the condition matches then the IF function shows the final output. Here the final output is the value of D5 otherwise the output shows “Not Applicable”
Result: 123-VXZ
How to Count If a Cell Begins with a Specific Text in Excel
Here, we will count the cells which start with a certain text or data using the combination of LEFT and SUMPRODUCT functions. Follow the procedure below to execute this option.
- We set the desired text on cell G4.
- Select cell G5 to apply the formula here..
=SUMPRODUCT(--(LEFT(D5:D12,3)=G4))
Formula Breakdown
LEFT(D5:D12,3)
The LEFT function shows the 3 values from the beginning of the total dataset here as the range is D5:D12.
Result: [VSX, PEI, FRE, IUR, VSX, IUR, FRE, PEI]
SUMPRODUCT(–(LEFT(D5:D12,3)=G4))
The SUMPRODUCT function shows the number of match data. Here, the lookup value is VSX from cell G4.
Result: 2
Things to Remember
- The FIND function is case-sensitive so if you use the FIND function make sure the formula matches the case with the lookup value.
- While using the SUMPRODUCT function we use (–). This double unary controls which values should be included in the formula. It makes the formula more powerful.
Conclusion
In this article we have discussed 3 methods for Excel if cell begins with certain text then return value. Sometimes we need to find the cells which start with certain text. Here you will get every possible answer. Hopefully, you can execute the examples shown in this article. Please let us know in the comment section if there is any query or suggestions or you can also visit Exceldemy to explore more.
