How to search multiple partial values

[jkpd2000]

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.

[Aladin Akyurek]

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 ]

[jkpd2000]

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

[jkpd2000]

Aladin, Its not working

[Aladin Akyurek]

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.

[Aladin Akyurek]

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 ]

[jkpd2000]

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

thanks

[Aladin Akyurek]

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