3 in 1 extraction

[sythong]

Hello again to me pals out there

I have this id no which incoporates date of birth-state-sex

Eg
Name Ref
Employee AJ 520123-07-5539

820123 (date of birth)
07 (state assumed Washington)
5539 (ref no)

For ref no if it is even (female); odd (male) based on
the last digit

I was wondering whether a formula can be generated
(perhaps combo of vlookup and if) that will spit out
in three different columns

Name DOB State Sex
Employee AJ 23 Jan 82 Washington F

Looks do-able?

Thanks to all who care to read this post!

 

[Scott Huish]

Post deleted.

 

[Krishnakumar]

Hi,

Book2
	A	B	C	D	E
1	Employee AJ 820123-07-5539	Employee AJ	23 Jan 82	Washington	F
Sheet3

Formula in B1,

=REPLACE(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,255,"")

In C1,

=REPLACE(REPLACE(19&MID(A1,LEN(B1)+2,6),5,0,"/"),8,0,"/")+0

In D1,

=LOOKUP(--MID(A1,LEN(B1&C1)+4,2),{1,2,3,4,5,6,7},{"St1","St2","St3","St4","St5","St6","Washington"})

You can replace the array values with a range

In E1,

=CHOOSE(--ISEVEN(RIGHT(A1)+0)+1,"F","M")

HTH

 

[sythong]

Krish

Nandrill.

I will work on it.

Best regards.

 

[Peter_SSs]

sythong

Some alternative formula suggestions:
B1: =LEFT(A1,FIND("-",A1)-8)
C1 (same as Kris): =REPLACE(REPLACE(19&MID(A1,LEN(B1)+2,6),5,0,"/"),8,0,"/")+0
D1: =VLOOKUP(MID(A1,FIND("-",A1)+1,2),$H$1:$I$51,2,0)
This assumes there is a lookup table in columns H & I with the state numbers stored as text so as to display the leading zeros in states 1 to 9.
E1: =IF(ISODD(RIGHT(A1)),"F","M")

Mr Excel.xls
	A	B	C	D	E	F	G	H	I
1	Employee AJ 820123-07-5539	Employee AJ	23/1/1982	Washington	F			State No	State
2								01	Utah
3								02	Texas
4								03	Ohio
5								04	New York
6								05	California
7								06	Wyoming
8								07	Washington
9								08	Florida
10								09	Alaska
Employee Data

 

[sythong]

Peter

Thank you for the suggestion.

Will try this too!!

Cheers

 

[sythong]

Krish & Peter

The formula worked. Would appreciate your help again.
Since the db I have is made up of Name and ID seperated
into two columns,what formula would you suggest if it is purely

820123-07-5539

Thanks again

 

[Krishnakumar]

Hi,

In B1,

=REPLACE(REPLACE(19&LEFT(A1,6),5,0,"/"),8,0,"/")+0

In C1,

=LOOKUP(--MID(A1,LEN(B1)+3,2),{1,2,3,4,5,6,7},{"St1","St2","St3","St4","St5","St6","Washington"})

In D1,

=ISEVEN(RIGHT(A1))+0

Custom format the cell as;

[=0]"F";[=1]"M";

HTH

 

[sythong]

You guys are extremely great
Thanks