Records ending in a "2"

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
 
N

Nikos Yannacopoulos

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

CLR

How about Data > Text to Columns........, using the comma as the separator?

Vaya con Dios,
Chuck, CABGx3
 
K

Ken Schmidt

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
 
Top