Identifying cells without certain text strings
 

Hello,

I have a list of codes in column B4:B263 as follows:

07129 B4 DT
07129 B4
07129 B4 TH
07555 A1
06999 A1 DT
etc.

I'd like to set up a list in column C that only includes the rows in column
B without the text "DT" or "TH", i.e. C4, C6 and C8 would be empty. Any
suggestions would be appreciated.

Thanks,

Jay

Identifying cells without certain text strings
 

Try this:

C4: =IF(MAX(--(RIGHT(B4,2)={"DT","TH"})),"",B4)
Copy that formula down as far as you need.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"jay" wrote in message
...
Hello,

I have a list of codes in column B4:B263 as follows:

07129 B4 DT
07129 B4
07129 B4 TH
07555 A1
06999 A1 DT
etc.

I'd like to set up a list in column C that only includes the rows in
column
B without the text "DT" or "TH", i.e. C4, C6 and C8 would be empty. Any
suggestions would be appreciated.

Thanks,

Jay

Identifying cells without certain text strings
 

hi
try this
=IF(OR(RIGHT(B7,2)="DT",RIGHT(B7,2)="TH"),"",B7)
or if you data is consistance as your example data, this might work too.
=IF(LEN(B7)9,"",B7)

edit to fit your data

regards
FSt1


"jay" wrote:

Hello,

I have a list of codes in column B4:B263 as follows:

07129 B4 DT
07129 B4
07129 B4 TH
07555 A1
06999 A1 DT
etc.

I'd like to set up a list in column C that only includes the rows in column
B without the text "DT" or "TH", i.e. C4, C6 and C8 would be empty. Any
suggestions would be appreciated.

Thanks,

Jay

Identifying cells without certain text strings
 

Slightly shorter

=IF(OR(RIGHT(B7,2)={"DT","TH"}),"",B7)


"FSt1" wrote:

hi
try this
=IF(OR(RIGHT(B7,2)="DT",RIGHT(B7,2)="TH"),"",B7)
or if you data is consistance as your example data, this might work too.
=IF(LEN(B7)9,"",B7)

edit to fit your data

regards
FSt1


"jay" wrote:

Hello,

I have a list of codes in column B4:B263 as follows:

07129 B4 DT
07129 B4
07129 B4 TH
07555 A1
06999 A1 DT
etc.

I'd like to set up a list in column C that only includes the rows in column
B without the text "DT" or "TH", i.e. C4, C6 and C8 would be empty. Any
suggestions would be appreciated.

Thanks,

Jay