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). Continue reading
Comments on: Excel: If cell contains formula examples
by Svetlana Cheusheva, updated on
Comments page 5. Total comments: 244
Hi, Please can someone assist with a formula that checks 2 separate columns and if the text is an exact match then it must return a a text from the separate column and also a value to the correct column.
-3.1441E-07x5
+ 9.9711E-05x4
- 1.2506E-02x3
+ 7.4496E-01x2
- 1.4013E+01x
+ 8.0642E+01
I am trying to use =IF(ISNUMBER(SEARCH("x",A1)), "LEFT(A1,FIND("x",A1)-1","A1") formula to look at each set of data and see it there is an "x" in the data string. If there is, I need to delete everything on the left of the "x", including the "x". However, if there is no 'x' I need the formula to sinply copy the data string as is, to the next cell. I may not have everthing just right but I now that the ISNUMBER(SEARCH is correct, I get TRUE, FALSE as I should. I have not been able to pair the formula with the rest, due to excel assuming that the "x" in the Left/Find statement is supposed to be a "*". Is there a way around this?
Thank you
HI, I need help with totaling up a number meeting a certain percent and the ablity to excluding any zeros. I can do the countif to obtain the percentage that achieves the required percent but I don't know how to exclude the zeros.
example: % achieving 80% w/o "0"
100
85
0
80
80
0
I have a budgeting document that reflects a few sub-budgets. I want there to be an overall balance column (G) and then 3 sub-budgets (I/J/K). I'd like a formula in I/J/K that states the following:
If column B includes the following partial text xxx (the budget indicator code), then subtract F from the row above it. (F is the amount spent).
The column B will include things like TS1, TS2, TS3 or HS1, HS2, HS3. (The TS and HS are the partial texts I want it to look for - with columns I and J being the TS and HS balance columns.)
Hi , I have a list of products with the cost for each product is written in the adjacent cell . what I want is that when I type the name of the product in another worksheet , the value of the cost appears automatically . Can anybody please help me to find the exact equation for that ? thanks in advance
Hello
Is there a way of using the below formula, but rather than have it search for the specific text only within a cell, it can search a sentence containing "apple" or "banana" etc then return the value based on the sentence content? I need the formula to be able to search for multiple fruits and return the value in another cell depending on what fruit it found within the sentence.
For example, cell A1 contains the sentence, "Mr Smith ate an apple".
cell B1 should then return Apple. However, if cell A1 contained, "Mr Smith ate a banana", cell B2 should return "Banana".
=IF(A2="apple", "Ap", IF(A2="avocado", "Av", IF(A2="banana", "B", IF(A2="lemon", "L", ""))))
Hope this makes sense!
Thank you
Hi Rhys,
COUNTIF with wildcards in the criteria works a treat:
=IF(COUNTIF(A2, "*apple*")>0, "Ap", IF(COUNTIF(A2, "*avocado*")>0, "Av", IF(COUNTIF(A2, "*banana*")>0, "B", IF(COUNTIF(A2, "*lemon*")>0, "L", ""))))
Thanks so much! Worked perfectly.
what would I use in the formula to lookup if a cell has text or number? (replacing the ISNUMBER) ISTEXT will not work as the cell can contain text or a number.
=IFERROR(IF(B17="","",IF(ISNUMBER(INDEX(T_E,MATCH(I_E,L_E,0),MATCH("ACT "&B17&" DT",L_H,0))),"R",CHAR(163))),"")
In your MATCH formula, what is the T_E , I_E and L_E? I believe that should be a range, but what range is it referring to?
Hello,
I have numbers on Column A1 that I need B1 to return with a name if the number matches
For example
A1 is 118 and B1 needs to be Chad
A1 is 132 and B1 needs to be Mike
A1 is 109 and B1 needs to be Tuan
A1 is 110 and B1 needs to be Kevin
A1 is 115 and B1 needs to be Carlos
A1 is 105 and B1 needs to be Mark
A1 is 107 and B1 needs to be Curtis
and so on, I have been fighting this all afternoon.
Use VLOOKUP formula
hi,
I came across an interesting problem need help to solve.
I have some text in Column A ( SKU ) and text to be searched in Column C ( Contains ), I need to search in SKU ( Column A) if any of the text listed in Contains ( Column C) need to insert value of Contains in Column B ( Print contains ) if none of the values in Contains ( Column C) is part of SKU ( Column A )then need to print No.
Expected result as below sample.
A B C
SKU Print contains Contains
--- ---------------- -----------
Dress-Blue-S dress dress
Tshirt-White-XL NO skrit
Skirt-Pink-XS skrit jeans
Skirt-Yellow-L NO
Tshirt-Black-M NO
Skrit-Yellow-L skrit
Jeans-Blue-XS jeans
Dress-White-S dress
Thanks in advance.
Hi, i found this platform very useful in my daily work. Kudos to the guys managing this site.
I came across a problem that I am unable to find solutions or rather i might not know how to search the problem.
I have 2 workbooks(Report and Checklist)
In Report I have 2 columns, Item, Person
In Checklist I have Columns for Items. (5 Items, Apple, Grapes, Banana, HoneyDew and Orange), I have rows for Adam, John and Tom
In the Report Workbook. It shows this (the pipe symbol is to separate the columns)
Item | Person
Apple | Tom
Apple | Adam
Orange | Adam
Orange | JOHN
...
Expected outcome (In Checklist Workbook)
I want to match in the column of Adam and Apple to show as "Yes" and so forth.
Thanks in advance
Hi, i need to find the amounts (column B) through finding the partial match (A2)to the long text (c2) and display the appropriate amount (column D).
LIST AMOUNTS LONG TEXT AMOUNT
HJA13784 ? abcd-HJA17561 09 082019 1,000,000
HJB02731 ? qwertyu-HJA13784 2019 08 2,500,000
HJA17561 ? plantferqfas sdsd ,HJA13784 3,000,000
SE18120347 ? asdfg sdg-SE10007894 4,000,000
Please help me on this? Thanks!
I have an order sheet containing, amongst other things, a description column and a value column. I need to put a comment such as "authorisation needed" in the description column when a value entered in the value column, in the corresponding line, is over £500.
I have tried conditional formatting and data validation but cannot get them to work together!
thinking I need some sort of "IF" formula but not well up on writing formulae.
any ideas would be appreciated - thanks
hi
I want to find if the cell contains NZ and then have the 3 numbers after the NZ as the result.
Example in cell D2 is DOL1003194 NZ101-05 in cell F2 I need the result to be 101
cell D3 is VOL10402 NZ102-077 in cell F3 I need the result to be 102
cell D4 is 51151317618 NZ112 in cell F4 I need the result to be 112
Be grateful for help
This is realy good if you have for example car models and you need to know what car it is
=IF(ISNUMBER(SEARCH("GO";A1));"MINI VAN";IF(ISNUMBER(SEARCH("YE";A1));"Bus";IF(ISNUMBER(SEARCH("L";A1));"Luksery line";"other")))))
and you can extend it as fares you need + it can have nr. of what year it was realised like you have L1 L2 L32 L13 L62 and its not important what nr. it is
if a cell contains 12 Digits, then I want it to return specific text.
Example:- If A2 contains 12 Digits then, B2 should say "Good".
Can you help?
I need a single formula that will say if there is a value in cell B1 then show 316, however if there is a value in C1 then show me 5000. I can get a formula that is on separate lines however, cannot get the formula in a single cell.
1 2
A Meter Number Amount
B 10HD00548 316
C 10HD00548 5000
Thank you.
Sorry, =MID(A1,FIND("USPM",A1),15)
The 15 or 19 should match the length of the text sting you want to return.
Found this very helpful - thanks
Is the following possible and if so what formula would I use to pull this off?
- Column A has rows of Summary data from problem tickets which will contain the problem ticket ID and other text.
- The problem ID will always be 15 characters in length
- The format of the Problem ID is USPM followed by the number for example USPM12345678911
Is there a formula that will look at for example cell A2 for *USPM* and return everything within the * * IN CELL b2? For example A2= USPM12345678911 the formula looks at A2 to see if it contains USPM and if it does it returns USPM and the next 11 charters to its right.
=MID(A1,FIND("USPM",A1),19)
Robert - You are my hero!!! Thanks so much. This worked like a charm :)
Hi! I am need help building a formula. I have a spreadsheet to be filled in with data. Account codes are across the top. I trying to find a formula string that will recognize which cell across has any text, then return the account number at the heading of column. Not sure if that explanation makes sense. I'm sure there has to be a formula to avoid doing this manually.
"which is found in B4" sorry for typo, I mean B2
Help, please! I need a solution
Problem: B2 contains "IT2". In B7 I want to be shown that number which is found in B4, so B7 should contain "2". What is the correct logical formula?
So again: if a cell (B2) contains a number, show in another cell (B7) THAT number.
Good morning
What i am trying to achieve is to count the number of full stops in a cell and return a number based on that, i.e if i have 1 full stop then it will return a 1 and if it has 3 full stops then it will return a 3, it may very well be that there are up to 10 full stops in a cell. The formula from your examples i am using is =IF(ISNUMBER(SEARCH($C$1,A1)),"1","")
This works great where C1 contains a full stop
HI,
I Have a column, M, named "Qualifications". It contains different strings of Academic qualification data. But I just need to pick the specific qualification. E.g If the string reads " Masters of Education", I just need "Masters" If it reads "Certificate of Secondary Education", I need KCSE, If it reads "Bachelors degree in Medicine", I just need "Bachelors".
Tried using the formula below but didn't work. PLease HELP
=LOOKUP(M2{"*Doctor*";"*Master*";"*Bachelor*";"*Diploma*";"*Secondary*";"*CSE*";"*EACE*";"*Primary*";"*CPE*";"*N/A*"} "PhD";"Masters";"Bachelors";"Diploma";"KCSE";"KCSE";"KCSE";"KCPE";"KCPE";"N/A"})
Teddy:
Wildcards can be used in some functions, but not in others. If you need to use the * in the formula you'll need to use VLOOKUP or an INDEX/MATCH formula.
Here's how to write a nested IF statement for the samples you provided:
=IF(A74="Doctor","PHD", IF(A74="Master","Masters",IF(A74="Bachelor","Bachelors", IF(A74="Secondary","KCSE", IF(A74="Diploma","KCSE", IF(A74="CSE","KCSE", IF(A74="EACE","KCSE", IF(A74="Primary","CPE", IF(A74="N/A","N/A")))))))))
You can use this as the basis for a huge IF/OR statement, but it would get crazy long.
Read the VLOOKUP or INDEX/MATCH articles here on AbleBits and see if that helps.
Hi,
I have two data tables with multiple row and column data. in table 1, i have alphanumeric code and dates while in table 2 i have similar alphanumeric code. i wanted to search the table 2 for any part of the alphanumeric code from table 1 and on locating the same, fetching me the date againt the said code.
Rajat:
Do you have sample data from each table you can post here? It's easier to try and help if I can see what you're working with.
Your suggestion on how to handle a cell that contains a specific string and do a partial match using combination of SEARCH, ISNUMBER and IF works like a charm! For example, my raw data input string in cell one is Apple, Ball, Cat, Dog and cell two is Apple, Dog. etc. etc. So I listed four separated columns in my modified data to store the results (1 or 0) if Apple, Ball, Cat or Dog is present or not in the string. I then reference the columns as tables and use a sumif to report on the respective tables. Works nicely. However I would like to pivot on the modified data and I want ONE field, not four. I would like one field showing table of Apple, Ball, Cat, Dog, Apple and Dog. One field name to be used by pivot table with six entries. How would I take the separated string results and put them back into a table so I can use a pivot table on that single table name?
I scrolled thru your samples but did not find a match for what I'm really trying to accomplish
Column A contain a scroll option box
If Scroll option in cell A1 matches cell J3-J6 then B1=Good
If Scroll option in cell A1 matches cell J7-J12 then B1=Bad
Thanks
Hi, Mind appears simply, however i have tried several funtions to know avail,HELP
What i am trying to do is,
DATE OF APPOINTMENT DATE DUE
so todays date, then in date due, i want the date to show 90 days.
=IF(D3<(TODAY()+90),"<<<","")
help
regards Debbie
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.