Excel IF statement that operates if a cell CONTAINS a string.

I do not know ho whether many of you have ever had this problem but as I was working on quite a lot of excel data, transforming it for placing in a database using local in file, you will occasionally find that there is data in excel that you may need to represent as a number to enable table linking and foreign keys. For the longest time, I was converting data for foreign keys either manually or using an application of database procedure to create these foreign keys.

This worked fine until I got many excel sheets that were not similarly formatted. So, instead of writing a procedure for each excel sheet being imported, I found a simple was of searching text in a cell and help create a ready to go sheet for importing to the database.

The excel if statement I used is as below

IF(SEARCH(“*dog*”, A1),”TRUE”,”FALSE”)

The above statement will search the cell A1 for the string value “dog” (the asterisks make sure that excel searches and finds the sting even if there are no spaces before or after the search parameter “dog’) and return TRUE if found of FALSE if not found. The values for TRUE and FALSE can of course be replaced with whatever values you may need to make things work for you.

I hope this has been of help. And as with nearly everything in the computing world. I know there are other ways and maybe even better ways of doing what I have mentioned above. So, please do comment or suggest more ways below.

Share

11 comments

  1. Good hint as I was looking how to hance If statements with text.

    However one problem. When I enter the above example into Excel 2010, if the word dog is in the cell, I get the “true”. If not I get the error “#value’ and not the “false” value. I have tried formating the cell from general to text but this does not change anything. The same error message occurs in Excel 2003. Any hints?

  2. Great web site. A lot of useful information here. I am sending it to several friends ans also sharing in delicious. And naturally, thank you for your sweat!

  3. IF(ISNUMBER(SEARCH(“*dog*”, A1)),”TRUE”,”FALSE”)

    This did get rid of the #Value Error, but it produced a #NAME? error in its place.

  4. Try this: =IF(ISERR(SEARCH(“*DOG*”,B5)),”False”,”True”)
    Note that true and false are reversed.

  5. To not get the “#value’ errors just change the quote marks to suit your location. i.e. UK English quotes look different.

  6. hi, any suggestion how to change the formula if there’s one string search involve ? Say If AI = string contains dog then true(dog) , or strings contains Cat then true(cat), and if false then blank (”). Im trying to build a data based based on system generated text , so i need to filter the text based on a simple text search

  7. This will give you what you asked for
    =IF(ISNUMBER(SEARCH(“*dog*”,A1)),”TRUE(dog)”,IF(ISNUMBER(SEARCH(“*cat*”,A1)),”TRUE(cat)”,””))

    Or you can do this which will return TRUE(cat & dog) if they both exist
    =IF(ISNUMBER(SEARCH(“*dog*”,A7)),IF(ISNUMBER(SEARCH(“*cat*”,A7)),”TRUE(cat & dog)”,”TRUE(dog)”),IF(ISNUMBER(SEARCH(“*cat*”,A7)),”TRUE(cat)”,””))

  8. Hi, help me pls.
    how can i find the number in a text / string?

    ex.

    column a
    SUBJECT
    RE: Mach 1 AIR SERVICES / SC 409074 / Prop. 803 – Direct Filing-Q3 Export GDSM Tiers – July 1, 2015
    RE: Mach 1 AIR SERVICES / SC 409074 / Prop. 803 – Direct Filing-Q3 Export GDSM Tiers – July 1, 2015
    RE: Senko USA Inc / SC 773220 / Prop. 22 – Direct Filing-Q3 Export GDSM Tiers – July 1, 2015
    RE: Worldwide Container Transfer (WCT) / SC 769004 / Prop. 39 – Direct Filing-Q3 Export GDSM Tiers – July 1, 2015

    column C
    SC NO
    773220
    769004
    409074
    409074

    Column B = Sc no 773220 will show column a – RE: Senko USA Inc / SC 773220 / Prop. 22 – Direct Filing-Q3 Export GDSM Tiers – July 1, 2015

  9. I’ve been following this string and trying to get the following to work for me: The formula is for cell I12. Cell F12 is either Breakfast, Lunch, Dinner, or Group. If F12 is breakfast, then I12 needs to = value in N12. If F12 is lunch, then I12 needs to equal value in O12. Dinner – P12 & Group – Q12 respectfully. Using the formula below, it only works if F12=breakfast. Any suggestions?

    =IF(SEARCH(“*breakfast*”,F12),N12,(IF(SEARCH(“*lunch*”,F12),O12,(IF(SEARCH(“*dinner*”,F12),P12,(IF(SEARCH(“*group*”,F12),Q12,0)))))))

    If anything other than breakfast is in f12, then the result is #value.

Leave a Reply