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.

**Table of Contents**hide

## 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**** f**unctions. 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.