by The tutorial provides a number of "Excel if contains" formula examples that show how to return something in another column if a target cell contains a required value, how to search with partial match and test multiple criteria with OR as well as AND logic.
One of the most common tasks in Excel is checking whether a cell contains a value of interest. What kind of value can that be? Just any text or number, specific text, or any value at all (not empty cell).
There exist several variations of "If cell contains" formula in Excel, depending on exactly what values you want to find. Generally, you will use the IF function to do a logical test, and return one value when the condition is met (cell contains) and/or another value when the condition is not met (cell does not contain). The below examples cover the most frequent scenarios.
If cell contains any value, then
For starters, let's see how to find cells that contain anything at all: any text, number, or date. For this, we are going to use a simple IF formula that checks for non-blank cells.
For example, to return "Not blank" in column B if column A's cell in the same row contains any value, you enter the following formula in B2, and then double click the small green square in the lower-right corner to copy the formula down the column:
=IF(A2<>"", "Not blank", "")
The result will look similar to this:
If cell contains text, then
If you want to find only cells with text values ignoring numbers and dates, then use IF in combination with the ISTEXT function. Here's the generic formula to return some value in another cell if a target cell contains any text:
Supposing, you want to insert the word "yes" in column B if a cell in column A contains text. To have it done, put the following formula in B2:
=IF(ISTEXT(A2), "Yes", "")
If cell contains number, then
In a similar fashion, you can identify cells with numeric values (numbers and dates). For this, use the IF function together with ISNUMBER:
The following formula returns "yes" in column B if a corresponding cell in column A contains any number:
=IF(ISNUMBER(A2), "Yes", "")
If cell contains specific text
Finding cells containing certain text (or numbers or dates) is easy. You write a regular IF formula that checks whether a target cell contains the desired text, and type the text to return in the value_if_true argument.
For example, to find out if cell A2 contains "apples", use this formula:
=IF(A2="apples", "Yes", "")
If cell does not contain specific text
If you are looking for the opposite result, i.e. return some value to another column if a target cell does not contain the specified text ("apples"), then do one of the following.
Supply an empty string ("") in the value_if_true argument, and text to return in the value_if_false argument:
=IF(A2="apples", "", "Not apples")
Or, put the "not equal to" operator in logical_test and text to return in value_if_true:
=IF(A2<>"apples", "Not apples", "")
Either way, the formula will produce this result:
If cell contains text: case-sensitive formula
To force your formula to distinguish between uppercase and lowercase characters, use the EXACT function that checks whether two text strings are exactly equal, including the letter case:
=IF(EXACT(A2,"APPLES"), "Yes", "")
You can also input the model text string in some cell (say in C1), fix the cell reference with the $ sign ($C$1), and compare the target cell with that cell:
=IF(EXACT(A2,$C$1), "Yes", "")
If cell contains specific text string (partial match)
We have finished with trivial tasks and move on to more challenging and interesting ones :) To check if a cell contains specific a given character or substring as part of the cell content, you can use one of these formulas:
Formula 1
Working from the inside out, here is what the formula does:
- The SEARCH function searches for a text string, and if the string is found, returns the position of the first character, the #VALUE! error otherwise.
- The ISNUMBER function checks whether SEARCH succeeded or failed. If SEARCH has returned any number, ISNUMBER returns TRUE. If SEARCH results in an error, ISNUMBER returns FALSE.
- Finally, the IF function returns the specified value for cells that have TRUE in the logical test, an empty string ("") otherwise.
Formula 2
Here, the COUNTIF function finds out how many times a certain text appears in a cell. To count partial matches, you place the wildcard character (*) on both sides of the text. If the count is greater than zero, then IF returns the specified value, otherwise - a blank cell.
And now, let's see how this generic formula works in real-life worksheets.
If cell contains certain text, put a value in another cell
Supposing you have a list of orders in column A and you want to find orders with a specific identifier, say "A-". The task can be accomplished with this formula:
=IF(ISNUMBER(SEARCH("A-", A2)), "Valid", "")
or
=IF(COUNTIF(A2, "*"&"A-"&"*"), "Valid", "")
Instead of hardcoding the string in the formula, you can input it in a separate cell (E1), the reference that cell in your formula:
=IF(ISNUMBER(SEARCH($E$1,A2)), "Valid", "")
or
=IF(COUNTIF(A2, "*"&$E$1&"*"), "Valid", "")
For the formula to work correctly, be sure to lock the address of the cell containing the string with the $ sign (absolute cell reference).
If cell contains specific text, copy it to another column
If you wish to copy the contents of the valid cells somewhere else, simply supply the address of the evaluated cell (A2) in the value_if_true argument:
=IF(ISNUMBER(SEARCH($E$1,A2)),A2,"")
The screenshot below shows the results:
If cell contains specific text: case-sensitive formula
In both of the above examples, the formulas are case-insensitive. In situations when you work with case-sensitive data, use the FIND function instead of SEARCH to distinguish the character case.
For example, the following formula will identify only orders with the uppercase "A-" ignoring lowercase "a-".
=IF(ISNUMBER(FIND("A-",A2)),"Valid","")
If cell contains, then return value – multiple conditions
A single ‘if cell contains’ statement is easy, right? But what if you need to check multiple conditions in the same formula and return different results? That is, if a cell contains some text, then return something, if it contains another text, then return something else, and so on.
To evaluate multiple conditions in a cell, you can use nested "if cell contains" statements. This allows you to create a chain of checks, with each condition leading to a specific result. Here are two generic formulas to achieve this:
Formula 1
Use the COUNTIF function to count how many times a certain text appears in a cell. If the count is greater than zero, then return the corresponding value. Otherwise, check the next condition.
Formula 2
Use the SEARCH function to find the position of a certain text in a cell. If the position is a number, then return the corresponding value. Otherwise, check the next condition.
For example, to checks if cell A2 contains "apple", "banana" or "lemon" and return the corresponding name of the fruit in cell B3, you can use one of these formulas:
=IF(COUNTIF(A2, "*apple*"), "Apple", IF(COUNTIF(A2, "*Banana*"), "Banana", IF(COUNTIF(A2, "*lemon*"), "Lemon", "")))
=IF(ISNUMBER(SEARCH("apple", A2)), "Apple", IF(ISNUMBER(SEARCH("banana", A2)), "Banana", IF(ISNUMBER(SEARCH("lemon", A2)), "Lemon", "")))
To fit your specific needs, you can extend the chain of conditions as necessary to handle more cases.
If cell contains one of many text strings (OR logic)
To identify cells containing at least one of many things you are searching for, use one of the following formulas.
IF OR ISNUMBER SEARCH formula
The most obvious approach would be to check for each substring individually and have the OR function return TRUE in the logical test of the IF formula if at least one substring is found:
Supposing you have a list of SKUs in column A and you want to find those that include either "dress" or "skirt". You can have it done by using this formula:
=IF(OR(ISNUMBER(SEARCH("dress",A2)),ISNUMBER(SEARCH("skirt",A2))),"Valid ","")
The formula works pretty well for a couple of items, but it's certainly not the way to go if you want to check for many things. In this case, a better approach would be using the SUMPRODUCT function as shown in the next example.
SUMPRODUCT ISNUMBER SEARCH formula
If you are dealing with multiple text strings, searching for each string individually would make your formula too long and difficult to read. A more elegant solution would be embedding the ISNUMBER SEARCH combination into the SUMPRODUCT function, and see if the result is greater than zero:
For example, to find out if A2 contains any of the words input in cells D2:D4, use this formula:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0
Alternatively, you can create a named range containing the strings to search for, or supply the words directly in the formula:
=SUMPRODUCT(--ISNUMBER(SEARCH({"dress","skirt","jeans"},A2)))>0
Either way, the result will be similar to this:
To make the output more user-friendly, you can nest the above formula into the IF function and return your own text instead of the TRUE/FALSE values:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0, "Valid", "")
How this formula works
At the core, you use ISNUMBER together with SEARCH as explained in the previous example. In this case, the search results are represented in the form of an array like {TRUE;FALSE;FALSE}. If a cell contains at least one of the specified substrings, there will be TRUE in the array. The double unary operator (--) coerces the TRUE / FALSE values to 1 and 0, respectively, and delivers an array like {1;0;0}. Finally, the SUMPRODUCT function adds up the numbers, and we pick out cells where the result is greater than zero.
If cell contains several strings (AND logic)
In situations when you want to find cells containing all of the specified text strings, use the already familiar ISNUMBER SEARCH combination together with IF AND:
For example, you can find SKUs containing both "dress" and "blue" with this formula:
=IF(AND(ISNUMBER(SEARCH("dress",A2)),ISNUMBER(SEARCH("blue",A2))),"Valid ","")
Or, you can type the strings in separate cells and reference those cells in your formula:
=IF(AND(ISNUMBER(SEARCH($D$2,A2)),ISNUMBER(SEARCH($E$2,A2))),"Valid ","")
As an alternative solution, you can count the occurrences of each string and check if each count is greater than zero:
=IF(AND(COUNTIF(A2,"*dress*")>0,COUNTIF(A2,"*blue*")>0),"Valid","")
The result will be exactly like shown in the screenshot above.
How to return different results based on cell value
In case you want to compare each cell in the target column against another list of items and return a different value for each match, use one of the following approaches.
Nested IFs
The logic of the nested IF formula is as simple as this: you use a separate IF function to test each condition, and return different values depending on the results of those tests.
Supposing you have a list of items in column A and you want to have their abbreviations in column B. To have it done, use the following formula:
=IF(A2="apple", "Ap", IF(A2="avocado", "Av", IF(A2="banana", "B", IF(A2="lemon", "L", ""))))
For full details about nested IF's syntax and logic, please see Excel nested IF - multiple conditions in a single formula.
Lookup formula
If you are looking for a more compact and better understandable formula, use the LOOKUP function with lookup and return values supplied as vertical array constants:
For accurate results, be sure to list the lookup values in alphabetical order, from A to Z.
=LOOKUP(A2,{"apple";"avocado";"banana";"lemon"},{"Ap";"Av";"B";"L"})
For more information, please see Lookup formula as an alternative to nested IFs.
Vlookup formula
When working with a variable data set, it may be more convenient to input a list of matches in separate cells and retrieve them by using a Vlookup formula, e.g.:
=VLOOKUP(A2, $D$2:$E$5, 2,FALSE )
For more information, please see Excel VLOOKUP tutorial for beginners.
This is how you check if a cell contains any value or specific text in Excel. Next week, we are going to continue looking at Excel's If cell contains formulas and learn how to count or sum relevant cells, copy or remove entire rows containing those cells, and more. Please stay tuned!
Practice workbook
Excel If Cell Contains - formula examples (.xlsx file)
240 comments
If cell G4 contains wording Baseline
Cell H19 mustn't add 5% (Costing)
If cell G4 contains wording Project
Cell H19 must add 5% (Costing)
Hi!
You can find the answer to your question in this article: Nested IF in Excel – formula with multiple conditions.
Need formula in Col B. eg. if B2 contains state listed in D2:D8 & Name of that state should be automatically added there.
| | | |
| A | B | C |
-----------------------------------------------------------------------------------------------------------------------------------------
| | | |
| | | |
| Address | States | Total States |
| | | |
| | | |
| Los Angeles, California, United States, 91311 | California | California |
| | | |
| Santa Clarita, California, United States, 91355-5078 | California | Florida |
| | | |
| Saint Petersburg, Florida, United States, 33701 | Florida | Texas |
| | | |
| Walnut Creek, California, United States, 94596-4410 | California | XYZ |
| | | |
| Roseville, California, United States, 95661 | California | XYZ1 |
| | | |
| Lake Forest, California, United States, 92630-8870 | California | XYZ2 |
| | | |
| Houston, Texas, United States, 92660 | Texas | XYZ3 |
Hi!
Sorry, I do not fully understand the task.
Please clarify your specific problem or provide additional details to highlight exactly what you need. What result do you want to get?
Please advise a number check formula that will yield "if lesser then" in a string scenario (dimensions in a cell) to find out if any of the numbers in their position are lesser then 5.
Cell Example: 11 x 3 x 4
Cell Formula Result: false true true
Hello!
To extract all numbers from text, use a user-defined function RegExpExtract. You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex).
=TEXTJOIN(" ",,(--RegExpExtract(A5,"\d+") < 5))
To combine results in a single cell with a delimiter, use the TEXTJOIN function.
You can also extract all numbers from text using Extract text tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hi Dear,
I have an excel file where a cell can contain 2 languages (Arabic and English). Is there a way to highlight which of these cells in the sheet have 2 languages. Also, is there a way to further determine if the English language (word) starts at the beginning of the sentence.
The problem is that we are working on a project to create an app, some sentences should include popular english words followed by arabic words. But whenever the sentence start with english, a problem appears in our user interface. That's why I need to determine which sentences in the cell start with and English word.
Thank you!
Hello!
You can determine the ANSI code of the first character in a text. The ASCII value of the lowercase alphabet is from 97 to 122. And, the ASCII value of the uppercase alphabet is from 65 to 90. To do this, use the CODE function.
=CODE(LEFT(A1,1))
See CODE function example here.
I hope my advice will help you solve your task.
Thank you so much Alexander. This helped me a lot!
Hi, need help with a formula, please:
If cell contains certain text, put a value in another cell: So that would be =IF(ISNUMBER(SEARCH("Sub",A2)),"Approve","")
But, I would like to use such a formula to look for "Sub" in a range of cells, for example, A2:F2 (1st case scenario), and then in a different instance, to look for "Sub" in a group of cells - A2, C2, D2 and G2 (2nd case scenario).
Thank you very much in advance.
Stan
Hello!
Try using the SUM formula to get the search result in a range.
=IF(SUM(--ISNUMBER(SEARCH("Sub",A2:F2))),"Approve","")
I hope it’ll be helpful.
Kind greetings and thank you very much for your kind help / response. I tried the equation that you helped provide above but it still provides a blank response, even when one of the cells contains "Sub".
In this case, I'm trying to approve the the row #3 because one of the cells from C3 to G3, in this case E3, contains the string "Sub".
A B C D E F G H
1 Alt ID Plans CovA CovB CovC CovD CovE Approval
2 101020 Pol3 Plan11 Coord2e
3 907030 Pol Sub5a Alt24
4 805050
5 778050 Plan88 Sub7d Coord2
6 232520 Sub4 ALt4 Plan6
7 357031 Plan2d Sub7e
So, I used =IF(SUM(--ISNUMBER(SEARCH("Sub",C3:G3))),"Approve","") but it still gives a blank response.
Thank you again, in advance.
Stan
Hi!
I have used your data. The formula works.
Hi, thank you very much for your kind help. I read one of your blogs on Excel ISNUMBER function with formula examples, under conditioning with SUMPRODUCT and it helped.
The equation I'm using now is =IF(SUMPRODUCT(--ISNUMBER(SEARCH("Sub",C3:G3))),"Approve","") and it is helping with what I needed to do.
Again, your very kind help is much appreciated.
Hello!
You didn't specify this, but I'm assuming you're working with Excel 2019 or 2016. The formula returns an array that needs to be summed. In Excel 365, you do not need to do this.
Sorry about that; and yes, I'm working with Excel 2019.
Again, I thank you.
Best regards,
Stan
Hi Alexander,
I am new to extended formulas. I usually manage with Vlook Up and Pivots. I am trying to show value of cell B2 in cell C2, if Text in Cell A2 is specific and if not, then value in Cell C2 must reflect "0" / zero. Likewise, for Row no.s 3, 4, 5, ....
Eg., A2 value is "Opening Balance", B2 value is "100" then C2 must reflect the value of Cell B2 i.e., "100". But if A2 value is NOT "Opening Balance" then C2 must reflect "0" / zero in numerical value. Please help me.
A B C D E F
Transactions Amount Opening Balance Invoice Debit Note Receipt
1 Opening Balance 100.00 100.00 0 0 0
2 Invoice 248.00 0 248.00 0 0
3 Debit Note 10.00 0 0 10.00 0
4 Receipt 238.00 0 0 0 238.00
Thanks,
Anand
Hello!
For your task, you can use the IF function.
IF(A2="Opening Balance", B2, 0)
If the suggested formula doesn't work for your case, feel free to describe it in detail.
Great information. Is it possible to put a formula in a cell that tests a different cell and places text in a 3rd cell ?
Example: Formula is in cell E1, testing cell A1 is equal to "Test", setting cell B1 equal to "Yes"
Cell B1 has other text in it until cell A1 has been changed to "Test" manually.
If, Then , Else structure but being able to specify the cells for the then and Else output.
I used this structure but can not specify a target cell
Cell B1 is =IF(ISNUMBER(SEARCH("Test,A1)),"Yes","No") will set B1 to Yes or No
but wish to add target cells for results:
Formula is in cell E1 =IF(ISNUMBER(SEARCH("Test,A1)),("Yes",B1),("No",C1)) Target cells are B1 and C1
I know how to do it in Macro but then need to run the macro. I wanted it to happen without running or adding macro.
Thanks,
Vin
Hi!
It has already been written many times in this blog that an Excel formula can only change the value of the cell in which it is written. For all other tasks, use VBA.
Hello, I would like to seek help. If a cell has a text value: then perform another formula else blank
Hi!
To check if a value is text, use the Excel IF ISTEXT formula.
I know how to count a series of keywords in Excel. I use this formula: =SUMPRODUCT(--ISNUMBER(SEARCH($CE$2:$CE$43,(G2:AP2))))
However, what would be the Excel formula if I want to count the number of keywords that exist only within +/-3 words around "risk" in the selected rows?
Consider this sentence: "Political uncertainty generates economic risk which stagnates economic activities." If my keywords are "political", "uncertainty", "stagnates", and economic", the total number of keywords within +/- 3 words around "risk" will be 3, i.e., "uncertainty", "stagnates", and "economic". "political" will be excluded since it is out of range.
IF the cell contains numbers and characters and without numbers , how can i filter only character cells please help me
Hello!
I recommend using a custom function REGEXMATCH. You can find the examples and detailed instructions here: How to use regex to match strings in Excel. You can filter on the TRUE value that this function will return.
this function selected another word that we don't need because the two (alphabet) first of this word are the same (ALbendazole and ALcid) any suggestions pls?
Hi!
I can't guess which formula and which values you are using. Please describe it in detail and I will try to help.
Please help to write a formula for the below
If I update the formula in I2 cell to =IF(SEARCH("HOSB",H2),"PO",""), the result is coming correctly, but if I change it to =IF(SEARCH("HOSB",H2),"PO",IF(SEARCH("HONB",H2),"Non PO",IF(SEARCH("HOCB",H2),"Contract", IF(SEARCH("HORB",H2),"Retention","")))) I am getting an error stating #VALUE!
Hello!
If the text is not found in the cell, the SEARCH function will return an error. Add an ISNUMBER function to your formula. In case of a successful search, it will return TRUE, in case the text is not found, it will return FALSE.
=IF(ISNUMBER(SEARCH("HOSB",H2)),"PO", IF(ISNUMBER(SEARCH("HONB",H2)),"Non PO", IF(ISNUMBER(SEARCH("HOCB",H2)),"Contract", IF(ISNUMBER(SEARCH("HORB",H2)),"Retention",""))))
hi,
I intend to use this formula and I got a comment "This formulate use more level of nesting than you can use in the current file formate." How to fix this formula?
=IF(ISNUMBER(SEARCH($M$4,I7)),$N$4,IF(ISNUMBER(SEARCH($M$5,I7)),$N$5,IF(ISNUMBER(SEARCH($M$6,I7)),$N$6,IF(ISNUMBER(SEARCH($M$7,I7)),$N$7,IF(ISNUMBER(SEARCH($M$8,I7)),$N$8,IF(ISNUMBER(SEARCH($M$9,I7)),$N$9,IF(ISNUMBER(SEARCH("ELECTRICITY",I7)),$N$10))))))
Hello!
The nested IF function has a limit. In Excel 2003 this is 7 levels, in later versions, it is 64 levels. Read more in the article: Excel nested IF statement - multiple conditions in a single formula.
Hi,
Kindly help please
A have a row for the report headers and below it a a row that says it is Mandatory or optional.
How so i check if the mandatory columns have value?
Thank you.
Hello!
To determine the cells that have values, you can use a combination of functions NOT(ISBLANK(A1)).
Please have a look at this article - Excel formula: if cell is not blank then.
I hope it’ll be helpful.
Hi! Thanks for the response.
But how do i make it dependent on the mandatory/optional row?
For example if the following column headers are the following: name, address,mobile,birthday.( last column is the checking, row complete?)
And then on the row below , all the fields are mandatory except for the birthday.
* row complete value should be TRUE if the mandatory cells are populated.
Thank you!
Hi!
Combine all of these conditions in an IF function as described in this guide: Excel IF statement with multiple AND/OR conditions.
IF(AND(NOT(ISBLANK(A1)),NOT(ISBLANK(B1)),NOT(ISBLANK(C1))),TRUE,FALSE)
Hi,
Thanks.
It would somehow look like this.
But i dont get how will the code be dependent on the second row. (By checking “Mandatory “)
Name Address Birthday Mobile Row complete?
Mandatory Mandatory Optional Mandatory
James ABC street 8171777 True
Alice Aaa streeat 10/10/1970 81666 True
Hi!
If I understand the problem correctly, you need to copy the formula to the second line and beyond.
Hi, the problem is that i need to check for row 2 as well( mandatory optional)
Starting from row 3( i need to check if there are blank values from the cells tagged as mandatory)
I actually have around 20 headers ( lets say from a1 to t1) and from a2 to t2 it says mandatory or optional.
Hi!
Use in the formula the addresses of only those cells that are mandated.
Hello,
I am trying to use a formula on a sheet with roughly 7900 rows that are constantly being added on to. I have part numbers/model numbers of different styles from different departments. Ones that are in the format of ###AAAAA* (3 numbers followed by letters of various lengths) to show in the column "1" as "INDST". I would also like the part numbers that are ####-###* (4 numbers followed by a dash and more numbers of various lengths) to show in column "1" as AERO. If the part numbers do not fit these requirements, I would like them to show as "ASSY"
Additionally,
A separate situation I Have is that there are (4) Statuses that I Have in Column "2" they are complete, Firm, Released, Stopped. I would like to have them all on the original sheet as well as separate sheets for each "Status".
Finally,
I Have due dates in Column "3" that I would like to change color from "Green" if the date has not passed, to "Red" if they are late, and I would like them to retain their color, but not update again if the job is in the "Complete", "Status" for record keeping later on.
If there is a way that I can have this continue to update when new information is added/Changed, since each day rows are added and some change statuses from "released" to "complete" or another "Status". I have basic knowledge of VBA, but not with a Data Dump of this size each day. I know there is a lot here, I would appreciate any and all help on this one as I am new to this job and am working with unfamiliar with these part numbers. I have included a small part of the data below and have started to manually put in Dpmt. names
Dpmt. / Status / Due Date / last transaction / Job Date / Job / item
INDST Complete 1/12/2021 4/12/2021 E291200-03 316BSZ-A213
AERO Complete 2/3/2021 6/25/2021 E291204-01 7200-8739-RM
AERO Complete 2/3/2021 7/19/2021 E291204-02 7200-8739-SZ
AERO Complete 2/3/2021 6/8/2021 E291204-03 7720-8740-RH
AERO Complete 2/3/2021 6/8/2021 E291204-04 7720-8740-RM
AERO Complete 2/3/2021 6/24/2021 E291205-01 7200-8741-RM
AERO Complete 2/3/2021 6/23/2021 E291205-02 7200-8741-SZ
AERO Complete 2/3/2021 6/10/2021 E291205-03 7720-8742-RH
AERO Complete 2/3/2021 6/10/2021 E291205-04 7720-8742-RM
INDST Complete 1/14/2021 3/18/2021 E291210-02 311BRK7-A335
DIGI Complete 1/14/2021 3/30/2021 E291210-03 340CRF6-A392
INDST Complete 1/18/2021 3/31/2021 E291213-01 317BRH7CAFNGJ
DIGI Complete 1/19/2021 4/6/2021 E291220-01 240CUQ6-A461
CABLE Complete 2/1/2021 4/6/2021 E291220-03 MEC-CA
Complete 1/15/2021 3/30/2021 E291224-01 241DRX7CAFJGJ
Complete 1/15/2021 3/25/2021 E291226-01 340CPP2CAFK
Complete 1/15/2021 3/31/2021 E291226-02 340CSZ3CAFK
Hello!
To automatically populate column 1 based on part numbers, you can use regular expressions. The formula will be something like this:
=IF(RegExpMatch(F1,"\d{3}[A-Z]"),"INDST",IF(RegExpMatch(F1,"\b\d{4}-\d"),"AERO","ASSY"))
You can find the examples and detailed instructions here: Excel Regex: match strings using regular expressions.
I also think this article will be useful: Excel conditional formatting for dates & time.
I hope my advice will help you solve your task.
I want to use the IF function where the logical test references a cell/column that looks up a value on a separate spreadsheet. The logical test returns the value in the cell (which is the simple lookup formula) rather than returning the value that is looked up. How do I solve for this?
Hi!
Sorry, I do not fully understand the task.
The value in a cell that is looking for a value in another table is the value being looked up. Describe an example of what you want to do.
Hi Alexander,
I been going crazy trying to get this formula, if you could help me that me very appreciated.
What I am trying to do is
(b1+b2)/2 if b1 or b2 aren't entered don't divide just give me the number that was entered in b1 or b2
Thank you in advance
Hello!
Here is the article that may be helpful to you: Excel IF statement with multiple AND/OR conditions.
=IF(OR(B1="",B2=""),IF(B1="",B2,B1),(B1+B2)/2)
This should solve your task.
Hi Alexander,
Thank you so much for your help it help
Hi,
I need to find a formula where the number is contained within text in a different cell. For example:
Column A Column D
21 Address 21 London Road London
There are 2253 numbers which I need to find within 4955 cells, please help!
Many Thanks
Hello!
If I understood the problem correctly, you want to extract the number from the text. We have a special tutorial on this. Please see: How to extract number from string in Excel.
Hope this is what you need.
Hi,
The only issue is this is only taking it from the first column, I would like it to look in the whole of column D to find the matching one?
Many Thanks
Hi!
Copy the formula for each cell you want to extract numbers from. It is impossible to do this with a single Excel formula. If this is not what you wanted, please describe the problem in more detail.
I am trying to write a formula that allows me to do the following:
Column I has either USD or CDN dollars,
If I has USD then take Colum G Total price and times it by currency rate listed in T2 or the rate 1.20
Hi!
If I understand your task correctly, the following formula should work for you:
=IF(I1="USD",G1*T2,G1)
That was perfect - thank you so much I tried over 8 different variances of IF / OR and AND trying to get this to work. Your the kindest, thank you.
Thank you so much, that worked. So kind of you.
Greetings, I’m trying to do the same as Cecile and Rasit, add some categories to my checking account info.
I have one table named “Raw” that contains the raw data from my checking account. On a second tab (in the same file), I created another table named “IDtranslate”. I want to search for key words in the Raw Description and bring back a Short Description as a new column in my Raw table.
This formula seems really close to what I need:
=IF(SUMPRODUCT(
--ISNUMBER(SEARCH(IDtranslate[search text],[@Description],1)))=0,
[@Description],
"found")
Keep an eye on that value-if-false, “found” because that is the problem. The formula is in the “Short Description” column of my Raw table.
Here is a sample of my Raw table (I think if you copy and paste into a blank Excel table, it will parse itself out correctly):
Description Short Description
Withdrawal POS #759507, MEMO: LOWE'S #2681 630 W NFIELD DR BROWNSBURGCard found
Withdrawal POS #8886, MEMO: Wal-Mart Super Center 2786 WAL-SAMS AVONINCard found
Withdrawal Debit Card MASTERCARD DEBIT, MEMO: SUN CLEANERS AVON INDate 12/18/21 Withdrawal Debit Card MASTERCARD DEBIT, MEMO: SUN CLEANERS AVON INDate 12/18/21
Withdrawal Transfer To Loan 0001 found
Withdrawal ACH VONAGE AMERICA, MEMO: ID: CO: VONAGE AMERICAEntry Class Code: WEBACH Trace Number: Withdrawal ACH VONAGE AMERICA, MEMO: ID: CO: VONAGE AMERICAEntry Class Code: WEBACH Trace Number:
Withdrawal ACH BRIGHT HOUSE NET, MEMO: ID: CO: BRIGHT HOUSE NETEntry Class Code: TELACH Trace Number: found
Withdrawal Bill Payment #91, MEMO: AMAZON.COM3 SEATTLE WACard 5967 found
Withdrawal Debit Card MASTERCARD DEBIT, MEMO: ALDI 4405 AVON INDate 12/19/21 found
Here is a sample of my IDtranslate table:
search text ID category
aldi Aldi grocery
amazon Amazon Amazon
amica Amica insurance
bright house Bright House utilities
loan 0001 car loan car loan
lowe's Lowe's Home maint and improve
meijer Meijer grocery
mnrd Menards Home maint and improve
panda express Panda Express restaurant
paypal PayPal PayPal
vectren Vectren utilities
wal-m Wal-Mart grocery
What I want to do is replace the “found” term in my formula with the correct value from the “ID” column of my IDtranslate table. The very first line in the Raw table where the “search text” lowe’s was correctly found needs to bring back “Lowe’s” from the ID column.
I’ve tried replacing the “found” term with variations on IDtranslate[ID] (with and without @ tossed in there), but I keep getting spills or other errors.
If I can get that Short Description formula to work, then adding a category column to my Raw table with a vlookup will be easy.
Thoughts?
Hello!
If I understand your task correctly, the following formula should work for you:
=IFERROR(INDEX(E2:E12,MATCH(1,--ISNUMBER(SEARCH(D2:D12,A2)),0)),"")
Column E - ID
Column D - search text
Column A - Description
Hope this is what you need.
Yes!!! That works. I'm glad to see you used MATCH. I had played with SWITCH a little bit but I failed at that. Makes feel like I sort of had the right idea!
I am using excel to convert manual testing scenario sheets to automated xml files to test the Covid vaccine schedule and ensure our vaccine forecaster is functioning properly with the new rules. I need to find out if a formula within a cell is calling the DOB or the date of the last vaccine for the forecast and then use that to fill in the test description so I can more easily spot patterns in what causes unexpected forecasting returns. Basically I need a formula that says IF the formula in GN2 (earliest forecast date) contains a reference to E2 (DOB) then True else false. Is there anything that can do that for me?
Hello!
I recommend using the FORMULATEXT function. It will extract the formula from the desired cell and write it down as text. Then apply the SEARCH function
=ISNUMBER(SEARCH("E2",FORMULATEXT(F5)))
I hope my advice will help you solve your task.