Finding Characters

[Mike the Kiwi]

Hi MVP's and other smart people,

I have a column of data (A1 thru A11500) with names or addresses etc. I would like to know if there is a way to display "Delete"
or similar in the adjacent cell in the same row but the next column (B1 thru B11500) where any particular cell in column 'A'
contains certain characters amongst the data within that cell.

I have done this many times using the 'IF' formula when it necessarily matched the entire cell contents but it's only 1 or 2
characters contained within the contents of each cell that I need to match this time.

Hope you understand what I am getting at here

All help is appreciated.

Kindest regards
Mike the Kiwi

 

[Frank Kabel]

Hi Mike
if the characters you want to check are always at the same position you
may use the MID function.
e.g. =IF(MID(A1,2,2)="AB","DELETE",A1)

or you may use the FIND function. e.g.
=IF(AND(ISNUMBER(FIND("A",A1)),ISNUMBER(FIND("B",A1))),"DELETE",A1)

 

[Ken Wright]

In B1, put

=IF(COUNTIF(A1,"*bcd*")>0,"DELETE","")

and copy down

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Hi MVP's and other smart people,

I have a column of data (A1 thru A11500) with names or addresses etc. I would

like to know if there is a way to display "Delete"

or similar in the adjacent cell in the same row but the next column (B1 thru

B11500) where any particular cell in column 'A'

contains certain characters amongst the data within that cell.

I have done this many times using the 'IF' formula when it necessarily matched

the entire cell contents but it's only 1 or 2

 

[Mike the Kiwi]

Danke Schön Frank

Hi Mike
if the characters you want to check are always at the same position you
may use the MID function.
e.g. =IF(MID(A1,2,2)="AB","DELETE",A1)

or you may use the FIND function. e.g.
=IF(AND(ISNUMBER(FIND("A",A1)),ISNUMBER(FIND("B",A1))),"DELETE",A1)

 

[Mike the Kiwi]

Many Thanks Ken

In B1, put

=IF(COUNTIF(A1,"*bcd*")>0,"DELETE","")

and copy down

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------




like to know if there is a way to display "Delete"
B11500) where any particular cell in column 'A'
the entire cell contents but it's only 1 or 2