Records ending in a "2"

  • Thread starter Thread starter Ken Schmidt
  • Start date Start date
K

Ken Schmidt

I have rows of text records, with each cell containing 6 characters,
starting with 2 letters and ending with 4 numbers. In every row, exactly
one record ends in a "2". In a separate column, I would like to pull out
the contents of that cell. For example, if the row contains BJ2655, EA1862,
and DL1911, I want the formula to return EA1862. Thanks for any help.
Using Excel 97.

Ken
 
If you don't want to use VB, then the easy way to do it
with plain worskheet functions is:
=CONCATENATE(IF(RIGHT(A1,1)="2",A1,""),IF(RIGHT(B1,1)
="2",B1,""),IF(RIGHT(C1,1)="2",C1,""))
assuming your values in columns A thru C.

Nikos Y. (nyannaco at in dot gr)
 
How about Data > Text to Columns........, using the comma as the separator?

Vaya con Dios,
Chuck, CABGx3
 
Thanks for all the replies. Stephen's formula did the trick. Thanks
Stephen! And yes, JMay, this can be done with the array formula
=IF(RIGHT(A1:C1,1)="2",A1:C1,"") , but I wanted a one cell solution.

Ken
 
Back
Top