conditional format based on first 3 letters of a name

[bmuddy2]

good morning
i have a long list of names in column A.(A2:A400 actually) im having trouble finding the name i want. can i type the first 3 letters of a name or just the first name in A1 and have the matching cell fill color yellow? there are many cells in that column that are blank or filled with numbers.

bill smith would fill yellow if i type bill or bil, whatever is easier.

 

[radian89]

Hi Bmuddy2

is this what you looking for?

the true/false formula is shown at F4, just type it on F5
the search you gonna type is on C2

the conditional formatting > select Cell C5 > apply conditional formatting "use formula to determine"
and click F5, make sure it's written $F5=TRUE, you need the $ only in front of the column, not in row.

try it.
have a nice day,

cheers

[/IMG]

 

[bmuddy2]

thanks for the reply, formula works fine, now trying to get the conditional formatting to work. question, each cell contains a first and last name, will this only work if the first and last name are entered in C2? or can i just enter in the first name? ( sorry wasnt clear when i mentioned the cell contents)

 

[radian89]

Hi,

Yes you can do it with last name.

example :
Bill Brown
Sue Moore
April May

lol

the search will works if you search it only part of 1st or Last name only

search example that success:
brown
bro
Moo
May

search example that won't be success :
Bill B
Sue M
Apr May


if you want to make the latter combination success, my suggestion you need to create 1 more column which there's no (_) space between first & last (you can do it by find & replace).
1. when its already combined (ex : BillBrown, SueMoore, etc)
2. place the true/false checking to the name column without space
3. while conditional formatting formula still the same,
4. you can now search it with (ex : BillB, SueM, AprilMay)

thanks

 

[MandeepBaluja]

Select cells and apply this formula =ISNUMBER(SEARCH($I$2,G5)) your search value should be in I2

 

[bmuddy2]

thank you