I have a spreadsheet where column A contains 4 character department codes
i.e. A123 and column B contains multiple 4 character department codes
separated by commas. I want to find the value A123 in either column A or
column B and if found put a Yes in column C and if not found then put a No in
column C for that row. I have tried several different functions and
combinations of functions but I can't seem to get reliable results.

Put this formula in C1 and then copy down as far as you like...

=IF(A1&B1="","",IF(ISERROR(SEARCH("A123",A1&B1))," No","Yes"))

Rick


"S Shipley" wrote in message
...
I have a spreadsheet where column A contains 4 character department codes
i.e. A123 and column B contains multiple 4 character department codes
separated by commas. I want to find the value A123 in either column A or
column B and if found put a Yes in column C and if not found then put a No
in
column C for that row. I have tried several different functions and
combinations of functions but I can't seem to get reliable results.

Well, technically, this formula could produce a false "Yes" (depending on
whether two department codes can be concatenated so that an embedded
department code can be found inside the concatenated text). This formula
should work no matter what your department codes look like...

=IF(A1&CHAR(1)&B1=CHAR(1),"",IF(ISERROR(SEARCH("A1 23",A1&CHAR(1)&B1)),"No","Yes"))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Put this formula in C1 and then copy down as far as you like...

=IF(A1&B1="","",IF(ISERROR(SEARCH("A123",A1&B1))," No","Yes"))

Rick


"S Shipley" wrote in message
...
I have a spreadsheet where column A contains 4 character department codes
i.e. A123 and column B contains multiple 4 character department codes
separated by commas. I want to find the value A123 in either column A or
column B and if found put a Yes in column C and if not found then put a
No in
column C for that row. I have tried several different functions and
combinations of functions but I can't seem to get reliable results.

Thanks Rick,

This solved the problem - I should have written the post earlier and I
wouldn't have pulled so much hair out.

"Rick Rothstein (MVP - VB)" wrote:

Well, technically, this formula could produce a false "Yes" (depending on
whether two department codes can be concatenated so that an embedded
department code can be found inside the concatenated text). This formula
should work no matter what your department codes look like...

=IF(A1&CHAR(1)&B1=CHAR(1),"",IF(ISERROR(SEARCH("A1 23",A1&CHAR(1)&B1)),"No","Yes"))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Put this formula in C1 and then copy down as far as you like...

=IF(A1&B1="","",IF(ISERROR(SEARCH("A123",A1&B1))," No","Yes"))

Rick


"S Shipley" wrote in message
...
I have a spreadsheet where column A contains 4 character department codes
i.e. A123 and column B contains multiple 4 character department codes
separated by commas. I want to find the value A123 in either column A or
column B and if found put a Yes in column C and if not found then put a
No in
column C for that row. I have tried several different functions and
combinations of functions but I can't seem to get reliable results.

Thanks Rick,

You are welcome.

This solved the problem - I should have written the post earlier
and I wouldn't have pulled so much hair out.

You wrote that to a bald man

https://mvp.support.microsoft.com/pr...Rick.Rothstein)

so you won't get too much sympathy for your missing hair from me.<bg

Rick