Thanks:  0
Likes:  0

# Thread: How to search multiple partial values

1. 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.

2. 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.

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.

[ 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. thanks aladin. can you help me with vb script for the same

5. 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. On 2002-05-09 09:22, jkpd2000 wrote:
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.

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

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

thanks

8. 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.

## User Tag List

#### Posting Permissions

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