Consider the following dataset of products and their IDs. We can search for a specific product using a predetermined value or a given value in a cell.

## Excel If Cell Begins with Certain Text, Then Return Value: 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. We want to find out if the cell begins with a certain text and then returns value using this dataset.

### Method 1. Combination of IF and LEFT Functions

- Select cell
**E5**and enter the following 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 length of the search term 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”)**

### Method 2 – Using IF and COUNTIF Functions

- Select
**cell D5**and apply the following formula there.

`=IF(COUNTIF(D5,"VSX*"),D5,"Not Applicable")`

- Drag down the
**Fill Handle**to apply the same formula in the entire dataset.

A **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 determines if there’s text starting with **VSX** or not in **cell D5**. If found, it returns **1**. Otherwise, it 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**

### Method 3 – Combination of IF, IFERROR, and SEARCH Functions

- Select cell
**F5**and apply the following formula.

`=IFERROR(IF(SEARCH(E5,D5,1)=1,E5,0),"")`

- Drag down the fill handle or double-click on the fill handle to copy it through the column.

**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 be located at the start 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 to avoid errors in results. Here, for any error formula will show a blank cell otherwise result in the **IF** function.

**Result: VSX-123**

**Note: **

When we need matching with case-sensitivity, use this formula:

`=IFERROR(IF(FIND(E5,D5,1)=1,E5,0),"")`

## If a Cell Ends with Certain Text, then Return Value in Excel

- Select cell
**E5**to apply the formula:

`=IF(RIGHT(C5,5)="Phone",C5,"Not Applicable")`

- Drag down the fill handle to execute this process.

## How to Check If a Cell Contains Certain Text in Excel

- 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.

## How to Check If a Cell Starts with a Number in Excel

- We have used the following formula on cell E5 and dragged the
**Fill Handle**icon.

`=IF(ISNUMBER(VALUE(LEFT(D5,2))), D5,"Not Applicable")`

**Formula Breakdown**

**LEFT(D5,3)**

**LEFT **function shows the 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

We will count the cells that start with a certain text or data using the combination of **LEFT **and** SUMPRODUCT** functions**.**

- Set the desired text on
**cell G4**. - Select cell
**G5**to apply the formula there.

`=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.

**Download the Practice Workbook**

**<< Go Back to Text | If Cell Contains | Formula List | Learn Excel**