Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Whether cells contains specific text or not?

  1. #1

    Question Whether cells contains specific text or not?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Everyone!
    I have a question to put on this forum.
    e.g.

    A1 = "cat 5 dog 8"
    A2 = "rat 3 bat 7"
    A3 = "hat 2 mat 9"

    and here is my question
    I want a formula that can return the number next to the specified text.
    like
    in B1 = if A1:A3 contains "dog" then the value should be 8
    in B2 = if A1:A3 contains "hat" then the value should be 2
    in B3 = if A1:A3 contains "cat" then the value should be 5


    Waiting for your early replies...

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Does your data have to be in that form, or could you put it in a table like this:

    A1 = "cat"
    B1 = "5"
    A2 = "dog"
    B2 = "8"
    etc...

    Reason I ask is that getting the info you want at that point will be VERY easy. Trying to do what you're asking is going to be difficult.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Assuming B1:B3 contain dog, hat and cat respectively, then in C1:
    =LOOKUP(1E+100,IF(ISNUMBER(SEARCH(B1,$A$1:$A$3)),-LOOKUP(2,-MID($A$1:$A$3,SEARCH(B1,$A$1:$A$3)+LEN(B1)+1,ROW($1:$255))),""))
    array-entered (with Ctrl+Shift+Enter) and copy down to C3.

  4. #4
    No, My data is in the form said by me above not like you.
    e.g. A1 contains cat 5 dog 8 --------> {cat} space {number} space {dog} space {number}
    A2 contains rat 3 bat 7 ---------> {rat} space {number} space {dog} space {number}
    and Onwards


    Quote Originally Posted by Ken Puls View Post
    Does your data have to be in that form, or could you put it in a table like this:

    A1 = "cat"
    B1 = "5"
    A2 = "dog"
    B2 = "8"
    etc...

    Reason I ask is that getting the info you want at that point will be VERY easy. Trying to do what you're asking is going to be difficult.

  5. #5
    Thanks Joe for your efforts.. but your assumption about my data formation is wrong. it is like my above reply.


    Quote Originally Posted by JoePublic View Post
    Assuming B1:B3 contain dog, hat and cat respectively, then in C1:
    =LOOKUP(1E+100,IF(ISNUMBER(SEARCH(B1,$A$1:$A$3)),-LOOKUP(2,-MID($A$1:$A$3,SEARCH(B1,$A$1:$A$3)+LEN(B1)+1,ROW($1:$255))),""))
    array-entered (with Ctrl+Shift+Enter) and copy down to C3.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Right, we understand how your data is. What we're asking is if it can be changed, or if you're bound to that format.

    The challenge we have is that Excel is built to work with different data in different columns. It loves tables, and doesn't really like having strings of data in one block to sort through. If you were looking for a value in one cell, it would be easy, but by trying to expand this over multiple cells it makes it very dificult. Joe's formula will work fine if your data is split, but array formulas (to deal with multiple conditions) don't support text functions, which we'd need to pull this off.

    The only way I can see to do this would be to write a custom function in VBA. Can certainly be done, but honestly, if you can convert your data to a more comlpiant format, it's going to be faster to develop and WAY easier to maintain.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    I think you misunderstood my formula. If you want hardcoded values, replace the B1 cell reference with literal text. It works on your data as is.

  8. #8
    No, I am bound to that format. I think we can split that data in to columns by using functions and then make appropriate formula..



    Quote Originally Posted by Ken Puls View Post
    Right, we understand how your data is. What we're asking is if it can be changed, or if you're bound to that format.

    The challenge we have is that Excel is built to work with different data in different columns. It loves tables, and doesn't really like having strings of data in one block to sort through. If you were looking for a value in one cell, it would be easy, but by trying to expand this over multiple cells it makes it very dificult. Joe's formula will work fine if your data is split, but array formulas (to deal with multiple conditions) don't support text functions, which we'd need to pull this off.

    The only way I can see to do this would be to write a custom function in VBA. Can certainly be done, but honestly, if you can convert your data to a more comlpiant format, it's going to be faster to develop and WAY easier to maintain.

  9. #9

    Red face

    WHOA..!!! You made it Joy..
    I am full of Joy now.. Hehee..
    WAIT......
    A problem is still there in this formula.
    e.g.
    A1 = Dog 15 cat 8

    this formula returns value of Dog with 1 instead of 15..
    Hope you find out the solutions ASAP.
    and Extremely Thankful to you for your assistence.




    Quote Originally Posted by JoePublic View Post
    Assuming B1:B3 contain dog, hat and cat respectively, then in C1:
    =LOOKUP(1E+100,IF(ISNUMBER(SEARCH(B1,$A$1:$A$3)),-LOOKUP(2,-MID($A$1:$A$3,SEARCH(B1,$A$1:$A$3)+LEN(B1)+1,ROW($1:$255))),""))
    array-entered (with Ctrl+Shift+Enter) and copy down to C3.

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Quote Originally Posted by JoePublic View Post
    I think you misunderstood my formula. If you want hardcoded values, replace the B1 cell reference with literal text. It works on your data as is.
    I love it when I stand corrected!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 2 1 2 LastLast

Posting Permissions

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