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
Thank you very much for this – What if we want to check if the cell contain this text?
=IF(COUNTIF(A1,”*This Text*”),”Yes”,”No”)
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
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).
How about start and end strings?
To Check if A Cell Starting with Certain String, use the following formula:
To Check if A Cell Ending with Certain String, use the following formula:
Thank you so much for your help guys you are really amazing.
You are Welcome!
Thank you for saving my time
You are Welcome!
Thanks
PNRao
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.
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.
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.
=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),""))
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.
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
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:
Step2: Return the Non Empty Data into Column E. Paste the Below Formula at Range E2 and Fill down:
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?
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
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!
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!
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.
Please help ^^
You can use either IF OR VLOOKUP Functions:
Using IF Function: Recommended when you have one or 2 items to check
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]
Hope this helps!
can i count the number of alphabets in a cell which contains alphanumeric code
‘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},)))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!
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
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 :
=IF(ISNONTEXT([@[eng read wb]]),CONCATENATE(Targets!$A$2,”,”,” “,[@Name],” “,Targets!$B$2),(IF(ISNONTEXT([@[eng read wts]]),CONCATENATE(Targets!$C$2,”,”,” “,[@Name],” “,Targets!$D$2),(IF(ISNONTEXT([@[eng read exs]]),CONCATENATE(Targets!$E$2,”,”,” “,[@Name],” “,Targets!$F$2),(IF(ISNONTEXT([@[eng read gds]]),CONCATENATE(Targets!$G$2,”,”,” “,[@Name],” “,Targets!$H$2),0)))))))
=IF(COUNTIF([@[eng read wb]],”ü”),CONCATENATE(Targets!$A$2,”,”,” “,[@Name],” “,Targets!$B$2),IF(COUNTIF([@[eng read wts]],”ü”),CONCATENATE(Targets!$C$2,”,”,” “,[@Name],” “,Targets!$D$2),IF(COUNTIF([@[eng read exs]],”ü”,CONCATENATE(Targets!$E$2,”,”,” “,[@Name],” “,Targets!$F$2),IF(COUNTIF([@[eng read gds]],”ü”),CONCATENATE(Targets!$g$2,”,”,” “,[@Name],” “,Targets!$h$2),0)))))
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
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
You can use one of the below formula:
Thanks
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
You can use the SUMIF formula to Sum based on criteria:
Thanks
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”)
You can use the below formula to check if Entire Cell value Equals to one of the Medication (Example:Medication1):
Use the below formula if to check with in Cell Value (Example: SomeText Medication1 SomeText etc):
Hope this helps!
Thanks
PNRao
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.
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:
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)),””)
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?
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.”
Hope this helps!
Thanks
PNRao
Hi,
I have a list of products with product numbers. I am working with a formula that staff can look up part of the product and be able to find the product name. I am using the VLOOKUP but staff has to enter the entire product name. This creates an issue because of spelling or not entering the entire name. Is there something I can use to help the search.
You can use the search function to check for given text and return all matched values:
Here: A2:A30 is the Products Range, E1 is your lookup/search string (where your users enter the part of the product).
Hope this helps!
On google sheets I have a table with SKU# header starting with either T, J, or GR I need a 1, 2, and GR to show in Category column to the left. Can you provide a formula for that?
Thank you
I have a small database with a fixed combination of criteria.
I would like to see automatically displayed (in an empty cell in another sheet) the name of the responsible, according to criteria (model and location). Can anyone help?
LOCATION MODEL RESPONSIBLE
51T06 U2 A
30200 U2 M
30200 G7 C
67601 U2 N
67531 U2 N
You use the below formula in the lookup sheet:
This formula assumes that you have data in the Data sheet.
In the Lookup sheet:
LOCATION is in A2.
MODEL is in B2.
RESPONSIBLE should appear in C2=XLOOKUP(1,(Data!$A$2:$A$100 = A2)*(Data!$B$2:$B$100 = B2),Data!$C$2:$C$100,”Not Found”)
Hope this helps!