Results 1 to 6 of 6

Thread: If a cell contains certain word.

  1. #1
    Board Regular jevi's Avatar
    Join Date
    Apr 2010
    Location
    Planet Earth
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb If a cell contains certain word.

    Hi All,

    I'm trying to do this formul but is not working.

    =IF(ISNUMBER(SEARCH("*LR 10*";A2));"M01";B2)

    so in column A2 I have the long desription of the loan and I want to find the ones that contain "LR 10", and if this is true to put in cell C2 "M01" otherwise the value that is in cell B2. But is not giving the result I want.

    So I need help...thank you
    Last edited by jevi; Sep 17th, 2019 at 06:58 AM.
    ---------------------------
    Excel 2016, "Always try as you will find the solution"

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: If a cell contains certain word.

    Hmm, seems to work for me. In what way is it "not giving the result I want"?
    Some sample data maybe?

    Also, whilst they won't do any harm, you don't need the asterisks in that formula.

    Note that my formula delimiter is "," not ";" like yours.

    SEARCH

    ABCD
    2Some long description including LR 10 and other textxxxM01M01
    3Some long description including LR 11 and other textyyyyyyyyy

    Spreadsheet Formulas
    CellFormula
    C2=IF(ISNUMBER(SEARCH("*LR 10*",A2)),"M01",B2)
    D2=IF(ISNUMBER(SEARCH("LR 10",A2)),"M01",B2)


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Sep 17th, 2019 at 07:05 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,080
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: If a cell contains certain word.

    If the data in col A has come from a website or some other 3rd party software, the spaces maybe non-breaking spaces, in which case try
    =IF(ISNUMBER(SEARCH("LR?10",A2)),"M01",B2)
    Although this will pick-up any instance where you have LR & 10 separated by a single character.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: If a cell contains certain word.

    Quote Originally Posted by Fluff View Post
    If the data in col A has come from a website or some other 3rd party software, the spaces maybe non-breaking spaces,...
    Good point, and we could target that &/or space specifically with

    =IF(ISNUMBER(SEARCH("LR 10",SUBSTITUTE(A2,CHAR(160)," "))),"M01",B2)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular jevi's Avatar
    Join Date
    Apr 2010
    Location
    Planet Earth
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If a cell contains certain word.

    Thank you to all of you for the answers. Now that I did the test from the home computer is working my formula but because I wrote the test LR 10 in the excel file while at office is not working. The test that i have in the office is in txt extracted from another program so I will use "?" and the other formula. I will try on monday and let you know if it is working, but I guess was not working as Pluff said as it is a text from another program.

    Have a nice weekend all of you.
    ---------------------------
    Excel 2016, "Always try as you will find the solution"

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,080
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: If a cell contains certain word.

    If you are likely to something like LRP10 in the cell and it should return B2 rather than "M01", then you are better off using Peter's code.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •