In Excel, while working with data there might be a situation when you need to check if the value in a cell starts with a particular text or if the starting part of that value is a number or not. In this tutorial, we will learn to write a formula to text both conditions.
IF a Cell Value Starts with a Particular Text
In the following example, you need to check if the starting three characters of the cell are equal to LXI. For this, you need to use the IF and LEFT.
You can use the below steps to write this formula:
- First, enter the IF function in a cell.
- After that, enter the LEFT function in the first argument of the IF, and refer to the cell A2 where you have the value, in the second argument, use 3 for the num_digit.
- Next, use the equal sign and enter the value (using double quotation marks) that you want to test.
- Now, in the second argument, enter “Yes”.
- And in the third argument, enter “No”.
- In the end, enter closing parentheses and hit enter to get the result.
As you can see below, for all the values where we have the value “LXI” at the start of the value, it returns Yes, else No.
=IF(LEFT(A2,3)="LXI","Yes","No")Count IF the Cells Start With
Use the below formula if you want to count the number of cells which starts with a particular value.
=SUMPRODUCT(--(LEFT(A2:A10,3)="LXI"))To learn more about this, you can learn about using SUMPRODUCT with a Condition.
If a Cell Value Starts with a Number
In the same way, you can write a formula to check if a cell starts with a number.
=IF(ISNUMBER(VALUE(LEFT(A2,2))), "Yes","No")In this formula, we have used the VALUE function around the LEFT. When you get a value by using the LEFT, it returns that value as a text, even if that value is a number.
That’s why to convert a number into a number we need to use the VALUE. After that, we used ISNUMBER to test the value returned by the LEFT. If that value is a number, the condition is TRUE, and IF returns YES, else No.