| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search If on text string
B2 through B6 contain a string consisting of last names:
B2 - Jamison, Gooden, Blatche, Foye, Miller B3 - Butler, Jamison, Blatche, Foye, Miller B4 - Butler, Jamison, Haywood, Foye, Miller B5 - Butler, Jamison, Haywood, Foye, Miller B6 - Jamison, Gooden, Haywood, Foye, Miller In column G, I want to place a formula that returns a 1 if "Gooden" is one of names listed in column B or a zero if "Gooden" is not listed. Can anyone help? Thanks, Bob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search If on text string
=if(iserror(search("Gooden",b2,1))=true,0,1)
Click yes if helped -- Greatly appreciated Eva "bob" wrote: B2 through B6 contain a string consisting of last names: B2 - Jamison, Gooden, Blatche, Foye, Miller B3 - Butler, Jamison, Blatche, Foye, Miller B4 - Butler, Jamison, Haywood, Foye, Miller B5 - Butler, Jamison, Haywood, Foye, Miller B6 - Jamison, Gooden, Haywood, Foye, Miller In column G, I want to place a formula that returns a 1 if "Gooden" is one of names listed in column B or a zero if "Gooden" is not listed. Can anyone help? Thanks, Bob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search If on text string
=SUMPRODUCT(--ISNUMBER(SEARCH("Gooden",B2:B6)))
"bob" wrote: B2 through B6 contain a string consisting of last names: B2 - Jamison, Gooden, Blatche, Foye, Miller B3 - Butler, Jamison, Blatche, Foye, Miller B4 - Butler, Jamison, Haywood, Foye, Miller B5 - Butler, Jamison, Haywood, Foye, Miller B6 - Jamison, Gooden, Haywood, Foye, Miller In column G, I want to place a formula that returns a 1 if "Gooden" is one of names listed in column B or a zero if "Gooden" is not listed. Can anyone help? Thanks, Bob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search If on text string
One way...
=COUNTIF(B2,"*Gooden*") Copy down as needed -- Biff Microsoft Excel MVP "bob" wrote in message ... B2 through B6 contain a string consisting of last names: B2 - Jamison, Gooden, Blatche, Foye, Miller B3 - Butler, Jamison, Blatche, Foye, Miller B4 - Butler, Jamison, Haywood, Foye, Miller B5 - Butler, Jamison, Haywood, Foye, Miller B6 - Jamison, Gooden, Haywood, Foye, Miller In column G, I want to place a formula that returns a 1 if "Gooden" is one of names listed in column B or a zero if "Gooden" is not listed. Can anyone help? Thanks, Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
| Search for a text string | Excel Discussion (Misc queries) | |||
| search text string within array | Excel Discussion (Misc queries) | |||
| Text search within a string using formula | Excel Worksheet Functions | |||
| Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Linear Mode
