Here is the the Excel formula to check if cell contains specific text then return value. Often, we need to search for a text in a cell or range of cells in the Excel, return if it contains a specific text or string. Following is the simple formula to find specific text in given cells and return its values.

## If Cell Contains Specific Text Then Return Value

Following is the Excel formula to find If Cell Contains Specific Text Then Return Value. This example used IF and COUNTIF functions to check for a value in a Cell and return required value.

`=IF(COUNTIF(A1,"*Specific Text*"),"Yes","No")`

You can see that we are counting the cells with specific text and returning the Value ‘Yes’ if found, ‘No’ if not found in the Cell.

### If Cell Contains Specific Text Then Return Value – Using COUNTIF Function

We can return the Cell value if using the same functions. You can also change the Specific Text to a Cell reference if you wants to make the function more dynamic.

`=IF(COUNTIF(A1,"*"&B2&"*"),A1,"")`

the above function returns the Cell Value if a specific text found in the Cell, otherwise it returns Blank.

### If Cell Contains Specific Text Then Return Value – Using SEARCH Function

You can also achieve this by using Search Function. Below is the Excel formula to find If Cell Contains Specific Text Then Return Value. In our example, we have input data in Cell A2:A12 and We will Return the Values in Range B2:B12.

Here the Excel formula to Return Value If Cell Contains Specific Text :

`=IFERROR(IF(SEARCH(B2,A2,1)>0,A2,0),"")`

Values Passed in the SEARCH Function

• Parameter 1: B2=”find_text”, the text where you can replace with the specific text to be searched in another text
• Parameter 2: A2=”within_text”, where you will search for ‘find_text’
• Parameter 3: 1=Character number which you want start you search in within_text from the left.

### Formula to Determine If Cell Contains Specific Text

Here is the simple formula to check if cell contains specific text or not. You can search for a text and return the required values (for example: Yes, No), if finding text existing in within cell.

`=IF(COUNTIF(A2,"*Specific Text*"),"Yes, it is Found", "Not Found")`

Here COUNTIF function determines if a specific text exist in a Cell. And the IF function return “Yes” if Found.

### Formula to Determine If Cell Contains Certain Text

You can use the following formula to Determine If Cell Contains Certain Text. You can change any text in the Cell B1 and you can see the result instantly.

`=IF(COUNTIF(A1,"*Certain Text*"),"TRUE","FALSE")`

We can make this more dynamic by passing certain text using a cell as shown below:

`=IF(COUNTIF(A1,"*" &B1 &"*"),"TRUE","FALSE")`

### Excel VBA If Cell Contains Certain Text

Here is the Excel VBA to check if Cell contains certain text. You can change the cell reference to any cell and specificText to any text.
``` Sub MacroToCheckIfCellContainsSpecificText_vba()```

``` Set Cell = Cell(1, 4) ' You can change this specificText = "Specific Text" ' You can change this ```

```If UCase(Cell.Value) Like "*" & UCase(specificText) & "*" Then MsgBox "Found" Else MsgBox "Not Found" End If End Sub ```

1. Dhuha April 26, 2022 at 10:51 am - Reply

Thank you very much for this – What if we want to check if the cell contain this text?

• PNRao May 3, 2022 at 2:09 am - Reply

=IF(COUNTIF(A1,”*This Text*”),”Yes”,”No”)

2. Lost in Excel June 15, 2022 at 6:45 am - Reply

If any cell in columns C;F have “x”, then return corresponding value (name) from row 1 to column B, on each row. Return all matches, if found.

If this would be just four columns and four rows, I’d do it manually, but my table has hundreds of rows and more than ten columns to check, so its too time consuming to do it manually.

Formula in Cell B2 [=IF(C2=””;””;”JOHN”)] returns only one result and the formula doesn’t accept more arguments to IF. How to do this?

This is what it should look like.
A B C D E F
1 John Mary Steve Sarah
2 Day 1 John, Mary x x
3 Day 2 Mary, Steve x x
4 Day 3 John, Sarah x x
….
n+1 Day n+1

• PNRao June 20, 2022 at 1:13 pm - Reply

You can use the TEXTJOIN and IF Function to get the corresponding (comma separated) list of the matched string:

`=TEXTJOIN(", ",TRUE,IF(C2:F2="x",\$C\$1:\$F\$1,""))`

This look for “x” in the Column C:F (Relative reference) and Join the all corresponding values from C1:F1 (absolute reference).

3. TJ June 17, 2022 at 12:55 am - Reply

How about start and end strings?

• PNRao June 20, 2022 at 1:00 pm - Reply

To Check if A Cell Starting with Certain String, use the following formula:

`=IF(COUNTIF(A1,"Specific Text*"),"Yes","No")`

To Check if A Cell Ending with Certain String, use the following formula:

`=IF(COUNTIF(A1,"*Specific Text"),"Yes","No")`
4. Fidele Niyongira June 21, 2022 at 11:29 am - Reply

Thank you so much for your help guys you are really amazing.

• PNRao June 21, 2022 at 4:56 pm - Reply

You are Welcome!

• Abhijeet February 23, 2024 at 5:28 am - Reply

Thank you for saving my time

• PNRao March 28, 2024 at 1:31 pm - Reply

You are Welcome!
Thanks
PNRao

5. Linda June 22, 2022 at 6:20 pm - Reply

I have a jumbled up column of data like this:
“2022 LI List”,”2021 CR Leadership”,”#CR”,”2021 IG List”,”Joe CR”
I want to put each item into its own column across the page so we can sort and analyze the data by row, by person the data is assigned to. It is currently a single column separated by comma and quotation marks and not in order. In the end I want each row to have a column for each item filled with that value.

• PNRao July 1, 2022 at 2:57 am - Reply

Please label the columns and provide the details about the example data:
“2022 LI List”,”2021 CR Leadership”,”#CR”,”2021 IG List”,”Joe CR”

We understand that items (1,2 and 4) in your data contains 3 columns, the 3rd one has only one, the last item have two. Please provide more details, how you wants to see in your output.

6. jay June 29, 2022 at 7:46 pm - Reply

Anyone know how I can combine these two statements so it will work? ‘
=IF(1,QUERY(StudentList!\$A:\$B, “Select B where A = “&E3,0),””)
=IF(2,QUERY(StudentList1!\$A:\$B, “Select B where A = “&E3,0),””)
When I put the first IF statement in the cell it works correctly, but when I try to add the 2nd IF statement followed by a comma it gives me an error message. Basically I need this formula to read another cell and depending on if the value in that cell is 1 or 2 I will either need it to go to the StudentList page or the StudentList1 page and grab the data. Thanks.

• PNRao July 1, 2022 at 3:10 am - Reply

``` =IF(1,"Process 1",IF(2,"Process 2",""))```

``` ```

```=IF(1,QUERY(StudentList!\$A:\$B, "Select B where A = "&E3,0),IF(2,QUERY(StudentList1!\$A:\$B, "Select B where A = "&E3,0),"")) ```

7. Pete July 7, 2022 at 3:58 pm - Reply

Hello
I have been trying all afternoon to figure out this equation but am lost. Am drafting an expense form where if a certain department is chosen VAT needs to be worked out.
Have already set up an equation to give me Yes or No is one of the departments is selected, then I need to use the gross sales amount of invoice and find 80% of this.

8. Nikola M August 23, 2022 at 5:00 pm - Reply

Hi, thanks for this tutorial. Now I have another problem – in column C I have a lot of blank spaces, and I want to make a table with data in it only. How do you make this?
i.e. with the CountIf function, in column C now I have (as mentioned above):
Some Text

Some Text
Some Text

Some Text
But I want to have a table with no blank spaces in between:
Some Text
Some Text
Some Text
Some Text

How to do this? Thanks

• PNRao September 11, 2022 at 7:12 am - Reply

Assuming your Data in Column C start from Row 2 (C2):

Step1: Return the Non Blank Index in Column D. Paste the Below Formula at Range D2 and Fill down:

```=IF(C2="","",MAX(D\$1:D1)+1)
```

Step2: Return the Non Empty Data into Column E. Paste the Below Formula at Range E2 and Fill down:

```=IFERROR(INDEX(\$C\$2:\$C\$11,MATCH(ROW()-ROW(\$E\$1),\$D\$2:\$D\$11,0)),"")
```
9. Mohammed Shahid October 5, 2022 at 1:21 pm - Reply

Hi, I want to return text from multiple sheets like, if sheet 1 has John in B2 and sheet has Jacob in B2, i want an output with both cells of sheet 1 and sheet 2 B2, but with condition of, if sheet 2 has Jacob, then ignore or empty.
=Sheet1!B2&” “&Sheet2!B2

How to do it?

• PNRao October 6, 2022 at 10:49 am - Reply
```=IF(AND(Sheet1!B2="John",Sheet2!B2="Jacob"),Sheet1!B2 &" " &Sheet2!B2,"")
```
10. Javier October 6, 2022 at 11:22 am - Reply

Hi,

I have some text on column A and I would like column B to show only a part of that string, but not all of it based on the text that I have on another tab. How can I do that?

Example:

Column A -> DATA_D_EUROPE
Column A -> DATA_1234_USA

Return:

Column B -> EUROPE
Column B -> USA

Another tab:

EUROPE
ASIA
USA

• PNRao December 5, 2022 at 11:05 am - Reply

Hi Javier,
You can use the below formula to achieve this:

```=TRIM(RIGHT(SUBSTITUTE(TRIM(A1),"_",REPT(" ",100)),100))
```

How this works:
=SUBSTITUTE(TRIM(A1),”_”,REPT(” “,100))
Replace _ (underscore) with 100 ‘ ‘ (space)

RIGHT(SUBSTITUTE(TRIM(A1),”_”,REPT(” “,100)),100)
Get last 100 characters

=TRIM()
Removes all Spaces

Hope this helps!

11. Neville February 3, 2023 at 10:06 am - Reply

I am new at this and this is probably basic.
If I record specific text eg letters TW in one of a column of cells then I want a specific cell on another sheet to record numerical value 2

In this case TW refers to twin calves!

• PNRao February 27, 2023 at 3:15 am - Reply
```=IF(A1="TW",2,"")
```
12. My March 3, 2023 at 11:37 am - Reply

Hi,
I can’t figure out how to get a modelnumber returned automatically in my B column based on text input in A column.

For example:
[Personal] in A column returns [G8] in corresponding cell in B column
[Shared] in A column returns [G2] in corresponding cell in B column

I’ve have googled and tried different formulas, but I must be overthinking this because I can’t get it to work.

• PNRao March 3, 2023 at 2:35 pm - Reply

You can use either IF OR VLOOKUP Functions:
Using IF Function: Recommended when you have one or 2 items to check

```=IF(A1="[Personal]","[G8]",IF(A1="[Shared]","[G2]","No Match Found"))
```

Using VLOOKUP Function: This is useful when you wants to check list of items to check
A B — E F
[Personal] =Formula Goes Here — [Personal] [G8]
[Shared] =Formula Goes Here — [Shared] [G2]

```=VLOOKUP(A1,\$E\$1:\$F\$2,2,FALSE)
```

Hope this helps!

13. christy March 17, 2023 at 4:22 pm - Reply

can i count the number of alphabets in a cell which contains alphanumeric code

• PNRao March 23, 2023 at 2:36 pm - Reply

‘Below are two formulas for Counting Numeric and Alphabets when you have Alphanumeric string in Cell A1

```
=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},)))

=LEN(A1)-SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},)))
```
14. Catherine April 20, 2023 at 7:12 pm - Reply

This is so helpful but not quite what I’m looking for, I think. I have a list of cells. If EVERY SINGLE CELL in a range is “Pass”, then I want my cell to reflect “Pass”. If ANY cell in that range contains “Fail”, then I want that cell to say “Fail”. If neither condition is true, then the cell should stay empty. How in the world do I do this please? Thank you in advance!

• PNRao April 22, 2023 at 2:38 am - Reply

Assuming that your target Range is A1:D10:

=IF(COUNTIF(A1:D10,”Pass”)=ROWS(A1:D10)*COLUMNS(A1:D10),”Pass”,IF(COUNTIF(A1:D10,”Fail”)>=1,”Fail”,””))
Thanks

15. A June 22, 2023 at 10:48 am - Reply

Help

Im trying to do a mail merge and get the sheet to concatenate a string of cells if a cell has certain text in it I have tried :

But neither is working properly, the ISNONTEXT produced a result, but the wrong result, and a basic nested IF won’t even calculate, neither will the COUNTIF
Thanks

16. RT December 12, 2023 at 11:52 am - Reply

Hi guys, looking to pull together a time keeping sheet, where time either worked or claimed back is recorded in column F (in format hh:mm), with a ‘Y’ in column G if it’s time worked, and a ‘Y’ in column H if it’s time claimed back, tried using =IF(G3=”Y”, I3=F3, IF(H3=”Y”, J3=F3)), but on success

• PNRao March 28, 2024 at 1:15 pm - Reply

You can use one of the below formula:

```=IF(G3="Y", "Worked", IF(H3="Y", "Claimed"))
=IF(G3="Y", I3, IF(H3="Y", J3))
```

Thanks

17. jeff December 13, 2023 at 7:00 am - Reply

A B C D E
I insurance 5 insurance 10 =IF(COUNTIF(CELLS B1:B6=”I”, ??
I insurance 5 donation 20
D donation 10 food sales 7
D donation 10 key club 38
FS food sales 7
KC Key Club 8
KC Key Club 30

i’m trying to get total of all insurance numbers into 1 cell (E1) using initials in A1
and donations in E2, food E3, etc

• PNRao March 28, 2024 at 1:23 pm - Reply

You can use the SUMIF formula to Sum based on criteria:

```E1=SUMIF(A:A,"I",B:B)
E2=SUMIF(A:A,"D",B:B)
E3=SUMIF(A:A,"FS",B:B)
```

Thanks

18. munira March 22, 2024 at 4:13 am - Reply

Hi,
I want a few texts to be identified. For example in a column there are Medication1, medication2, medication3..to medicaton100. I want the column to return Yes for only medication1,2,3. For example, I thought I should do the below, but didn’t work. I know no basic, but doing just 1 item worked.

=IF(COUNTIF(A1,”*Specific Text1*”,”*Specific Text2*”,”*Specific Text3*”),”Yes”,”No”)

• PNRao March 28, 2024 at 1:45 pm - Reply

You can use the below formula to check if Entire Cell value Equals to one of the Medication (Example:Medication1):

```=IF(OR(A1="Medication1",A1="Medication2",A1="Medication3"),"Yes","No")
```

Use the below formula if to check with in Cell Value (Example: SomeText Medication1 SomeText etc):

```=IF(OR(COUNTIF(A1,"*Medication1*"),COUNTIF(A1,"*Medication2*"),COUNTIF(A1,"*Medication3*")),"Yes","No")
```

Hope this helps!
Thanks
PNRao

19. Hossein April 21, 2024 at 10:47 am - Reply

Hi
I have a problem. I have a list of cities and a lot of cells that contains a city name. I want to check each cell and write city name in next cell.
for example:
cities list: “Paris”, “London”, “Washington”, …
cell value: “Hi my name is john and i live in Paris.”
I want to write Paris in next cell.
Thanks.

• PNRao April 23, 2024 at 12:46 am - Reply

Assuming that You have Data in Column A (Example A1=Hi my name is john and i live in Paris.) and You have list of cities in column C1:C3 (Example: “Paris”, “London”, “Washington”). And you want to check if any city name is matching in Column A and return that city in Column B. Here is the formula to place in Column B:

```=IF(SUM(IFERROR(SEARCH(\$C\$1:\$C\$3,A1,1),0))>0,INDEX(\$C\$1:\$C\$3,MATCH(TRUE,SEARCH(\$C\$1:\$C\$3,A1)>0,0)),"")
```
20. Nicky June 12, 2024 at 7:39 am - Reply

Hi, based on the below feedback already provided. How would I use the below formula with an exact case (upper, lower) match of the city list names(this list is on another tab). Also is there an equivalent xlookup or vlookup that could be used?

You have Data in Column A (Example A1=Hi my name is john and i live in Paris.) and You have list of cities in column C1:C3 (Example: “Paris”, “London”, “Washington”). And you want to check if any city name is matching in Column A and return that city in Column B. Here is the formula to place in Column B:

=IF(SUM(IFERROR(SEARCH(\$C\$1:\$C\$3,A1,1),0))>0,INDEX(\$C\$1:\$C\$3,MATCH(TRUE,SEARCH(\$C\$1:\$C\$3,A1)>0,0)),””)

21. ana July 24, 2024 at 2:13 pm - Reply

Hello, i am having below requirement.
my certain cell contains a description of what the associate spent money on. example, airline ticket, airport food, etc.

how do i use this, if the cell contains certain key words, then it classifies as ” travel meals” for example.

does If let you add more than one key word to return the value of a certain text?

• PNRao July 26, 2024 at 11:28 am - Reply

Yes! The below formula checks if the text in cell A2 contains either the word “food” or “meal.” It does this by using the SEARCH function to locate these keywords within the text. If SEARCH finds a keyword, it returns the position of the keyword, making ISNUMBER return TRUE. The OR function then checks if either ISNUMBER call returns TRUE. If at least one keyword is present, the IF function assigns the category “Travel Meals” to the cell where the formula is entered; if no keywords are found, it assigns “Other Category.”

``` =IF(OR(ISNUMBER(SEARCH("meal", A2)), ISNUMBER(SEARCH("food", A2))), "Travel Meals", "Other Category")
```

We recommend the below approach:
The below formula categorizes text in cell A2 by checking for keywords in cells D2 to D5. It uses the SEARCH function to find these keywords in A2, and ISNUMBER to confirm their presence. The MATCH function identifies the first true match, and INDEX retrieves the corresponding category from E2 to E5. If no keywords match, IFERROR outputs “Other Category.”

```=IFERROR(INDEX(\$E\$2:\$E\$5, MATCH(TRUE, ISNUMBER(SEARCH(\$D\$2:\$D\$5, A2)), 0)), "Other Category")
```

Hope this helps!
Thanks
PNRao