Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: How to search multiple partial values

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,
    I want to write a code to find a multiple partial values in a string and place 1 or 0 in the beside cell.

    ex. a1 = "ABCDEFG"
    a2 = "HGIJKLM"
    a3 = "ABCDEFG"

    i want to search for "CDE" or "EFG"
    and place 1 or 0 in the beside cell.

    the result should be

    b1 1
    b2 0
    b3 1

    i want it programatically so that i can automate. Please tell the instructions also of how to do it from beginning to end from making the code and puuting it and running it, as i am new to excel.

    thanks in advance.


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-09 09:03, jkpd2000 wrote:
    Hi,
    I want to write a code to find a multiple partial values in a string and place 1 or 0 in the beside cell.

    ex. a1 = "ABCDEFG"
    a2 = "HGIJKLM"
    a3 = "ABCDEFG"

    i want to search for "CDE" or "EFG"
    and place 1 or 0 in the beside cell.

    the result should be

    b1 1
    b2 0
    b3 1

    i want it programatically so that i can automate. Please tell the instructions also of how to do it from beginning to end from making the code and puuting it and running it, as i am new to excel.

    thanks in advance.

    If you're not looking for code in VBA perse,

    in B1 enter and copy down:

    =ISNUMBER(SEARCH($E$1,A1))+0

    where E1 houses a target substring to look for.

    If you want to look for 2 substrings at a time,

    In B1 enter and copy down:

    =(ISNUMBER(SEARCH($E$1,A1))+ISNUMBER(SEARCH($E$2,A1))>0)+0

    will look for substrings in E1 and E2 in one go.

    Addendum: If you'd like it case-sensitive, use FIND instead of SEARCH in the foregoing formulas.

    Postscript. Edited for locking E1 & E2 and the longer formula for extraneous paren and wrong ref.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-09 09:13 ]

    [ This Message was edited by: Aladin Akyurek on 2002-05-09 09:14 ]

    [ This Message was edited by: Aladin Akyurek on 2002-05-09 09:31 ]

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks aladin. can you help me with vb script for the same

  4. #4
    Board Regular
    Join Date
    May 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, Its not working

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-09 09:15, jkpd2000 wrote:
    thanks aladin. can you help me with vb script for the same
    Alas, no. Simply because I don't know VBA.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-09 09:22, jkpd2000 wrote:
    Aladin, Its not working
    I forgat to tell to freeze E1 and E2.
    Simply change them to $E$1 and $E$2.
    See the original reply I edited for that mistake and other omissions.

    Cheers.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-09 09:33 ]

  7. #7
    Board Regular
    Join Date
    May 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, is there a simple way. as i have to search for 20 different values in on cell

    thanks

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-09 09:49, jkpd2000 wrote:
    Aladin, is there a simple way. as i have to search for 20 different values in on cell

    thanks
    Yes.

    =(SUMPRODUCT((ISNUMBER(SEARCH($E$1:$E$20,A1)))+0)>0)+0

    where E1:E20 houses the substrings of interest.

    Aladin

Some videos you may like

User Tag List

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
  •