finding letters in text

[johngio]

Hi all,

I have a list of names, but some of the names have additional information written beside them. Eg;

Smith, john
peirce, roger
smith, john (division#3)
smith, john
shmitt, bob

What I want to do is scroll through the list and if the name matches a name that I will have as the title of the column, put a 1 in the cell. I.e.

smith, john pierce, roger shmitt, bob
Smith, john 1
peirce, roger 1
smith, john (division#3) 1
smith, john 1
shmitt, bob 1

I can do this easy enough with numbers using the following formula:

=IF(AND(INDEX('johns shizzle dizzle sheet'!$I$2,1,1)<=INDEX(CELL("row", $A2),1,1),INDEX(CELL("row", $A2),1,1)<=INDEX('johns shizzle dizzle sheet'!$I$3,1,1)),IF(($G2)<>"",IF(VALUE($G2)=Y$1,1,""),""),"")

$G2 is the row with the names in it. How do I modify this formula so it will place a 1 in the cell if the first 6 or 7 characters of the cell data match the name in the box? ? ? I don't know that specific command.

Thanks

John

 

[johngio]

don't worry - i figured it out using the LEFT function

 

[PA HS Teacher]

=ISNUMBER(SEARCH("Desired Name",A1)) * 1

Will return a 1 if "Desired Name" appears anywhere withing Cell A1