In this video we use the IF function of Excel to test if a cell contains specific text.
Usually with the IF function you test a cell for a complete match, but in this example we need a partial match on text.
We then extend the scenario to handle a problem and then test for two different specific text strings.
Find more great free tutorials at;
*** Online Excel Courses ***
The Ultimate Excel Course – Learn Everything ►
Excel VBA for Beginners ►
Advanced Excel Tricks ►
Excel Formulas Made Easy ►
Creating Sports League Tables and Tournaments in Excel ►
Connect with us!
Google+ ►
Facebook ►
Twitter ►
Nguồn: https://mirchigossips.in/
Xem thêm bài viết: https://mirchigossips.in/category/cong-nghe/
Quantum Age says:
Hi sir , How to seach " number " and then formula show current time ?
Ezhil Vakes says:
Hi Alan,
Thank you for the tutorial.
I am trying to achieve in the formula to show the following:
If one "CB2" is standalone to show "local", if "CB2 & "CB3" (combined) to show "local & far" and if "CB3" to show "far"
How can I achieve this?
TIA
Ole Thomas Lindebø says:
I'm sorry, but I can't seem to get the formula to function. Any tips? 🙂
Collum A2 has the text "abc" in it.
=if(ISNUMBER(SEARCH("abc" ,A2))"yes","no")
Rajesh shah says:
problem solved
Routhu Aravind says:
tnq
Jarryd Robbins says:
Thank you for the tutorial… IsNumber is what I was missing!
Paul Levine says:
What is i want
A1 is Paul Levine
B1 is Paul Levine Andrew
C1 is the formula to use to show TRUE as both are Paul Levine but additional text does not matter
How do i do this?
Gerald Robinson says:
Would there be a formula to identify the postcode and copy it to the next column for each address? ie] cell 2B would have CB5, cell 3B would have CB3, etc?
Dravidan says:
I want to know how to search the data in a different range of columns with if function? For example if A1 = any one cell in the range from d1:d5 then if true “Yes” if false “No”. Is this possible?
William Abraham says:
I am having an error, only for first few line items this formula is getting applied, I have a list of text/numbers if a cell contains any of these I want to see the specific text/numbers from the list in another cell, can anyone help
Alper Ak says:
thanks bro, greetings from istanbul
Commanderfury44 says:
How would you write a formula to look for specific text (lets say in this case "Hello"), take a numbers in another row that is with "Hello" and get an average of all of the "Hello" numbers together.
AQEEL AHMED says:
Perfect.
Tongsak says:
how to check if the cell has English letters? I have people register from many country and want to know how many people are speaking English.
Mohit Batu says:
can i use this formula more then 4 contains
Md.Saiful Islam Tuku says:
Thanks From Bangladesh
PALLAB MAHANTA says:
Hi Sir,
If I want to select more than 2 Specific Text. What will be the Formula? Could you please assist on this.
Olga Pulido says:
I've been trying to do the "if" function with a drop-down box but I can't get it to work. The ddrop down has the "yes" (with 5 points), "no" (0 points and "NA". I can't get it to work. Can that be done?
Sidney Schultz says:
"Excel-lent", thank you
filipe coutinho says:
Thanks a lot very useful. Can u help on this please. I have to enter (345) but sometimes I enter without () eg 345. Then I have to find which entries are negative number and correct.
AlbarS Timika says:
Dear Alan,
Please help me, How to Combine these Formulas:
1. IF(OR(OR(ISNUMBER(SEARCH({"*Ports*","*Port S*","*Amama*","*Bagh*","*Post*","*Coal*"},$E3))),"PORTSITE","Recheck")
2. IF(OR(OR(ISNUMBER(SEARCH({"*Cargo*","*CD*","*Steved*"},$E3))),"CARGODOCK","Recheck")
3. IF(OR(OR(ISNUMBER(SEARCH({"*28*","*Avco*","*Core*","*Base*"},$E3))),"MP28-BC-BANDARA","Recheck)
4. IF(OR(OR(ISNUMBER(SEARCH({"*31*","*32*","*LIP*","*Kuala*","*Yard*",},$E3))),"MP32-LIP","Recheck),"OTHER-Need to Remakesure" (Other then as OTHER-Need to remake sure)
Need to combine or shorten in a Cell..please…
For each Formula can function if separated, but I'm confused to combine it as a formula Excel just in a Cell.
Sudhanshu Mohanty says:
Is there a way to return the text that you are searching for in the cell? So for the example above it is looking for CB2. I have a list of search items and a range of text strings. I want to search for these items in the text strings – if it finds the value then output that value that it finds.