Extract data

[Matti Koski]

How can I extract the data (social security number) which is in one column.
The first six numbers are the birth date and then there is a separator - or
A (depending if your birth year is on 19 or 20th century. Then we have four
digits (numbers or letters). The third letter is a number which tells your
sex (boys number is odd and girls number is even. I would like to extract
the fist column as follows:



Col1 Col2 Col3



170456-0292 17.04.56 Boy

221201A1R51 22.12.01 Boy

210802AG22R 21.08.02 Girl

030398-T230 03.03.98 Boy

050605A1281 05.06.05 Girl



Thanks,



Matti

 

[Pete_UK]

You can extract the first part with this formula in B1 (assuming data
is in A1):

=LEFT(A1,2)&"."&MID(A1,3,2)&"."&MID(A1,5,2)

and in C1 for the second part:

=IF(MOD(MID(A1,10,1),2)=0,"Girl","Boy")

Copy down as necessary.

Hope this helps.

Pete

 

[Roger Govier]

Hi Matti

For column B
=DATE(1900+MID(A1,5,2)+(100*(MID(A1,7,1)="A")),MID(A1,3,2),LEFT(A1,2))

For column C
=IF(ISODD(MID(A1,10,1)),"Boy","Girl")

 

[Ron Rosenfeld]

How can I extract the data (social security number) which is in one column.
The first six numbers are the birth date and then there is a separator - or
A (depending if your birth year is on 19 or 20th century. Then we have four
digits (numbers or letters). The third letter is a number which tells your
sex (boys number is odd and girls number is even. I would like to extract
the fist column as follows:



Col1 Col2 Col3



170456-0292 17.04.56 Boy

221201A1R51 22.12.01 Boy

210802AG22R 21.08.02 Girl

030398-T230 03.03.98 Boy

050605A1281 05.06.05 Girl



Thanks,



Matti

With data in A1:

Col2: =TEXT(LEFT(A1,6),"00\.00\.00")
Col3: =IF(MOD(MID(A1,10,1),2)=1,"Boy","Girl")


--ron