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!

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 :