Finding Characters

  • Thread starter Thread starter Mike the Kiwi
  • Start date Start date
M

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
 
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)
 
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 :-)
----------------------------------------------------------------------------



Mike the Kiwi said:
Hi MVP's and other smart people,

I have a column of data (A1 thru A11500) with names or addresses etc. I would
Click to expand...
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
Click to expand...
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
Click to expand...
the entire cell contents but it's only 1 or 2
 
Danke Schön Frank

Frank Kabel said:
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)
Click to expand...
 
Many Thanks Ken


Ken Wright said:
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
Click to expand...
 
Back
Top