# finding letters in text

#### johngio

##### Board Regular
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
don't worry - i figured it out using the LEFT function

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

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

Replies
1
Views
178
Replies
4
Views
380
Replies
10
Views
709
Replies
3
Views
302
Replies
5
Views
453

1,203,681
Messages
6,056,710
Members
444,885
Latest member
Mark Prillman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back