# 3 in 1 extraction

#### sythong

##### Active Member
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!

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Post deleted.

#### Krishnakumar

##### Well-known Member
Hi,
Book2
ABCDE
1Employee AJ 820123-07-5539Employee AJ23 Jan 82WashingtonF
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

##### Active Member
Krish

Nandrill.

I will work on it.

Best regards.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDEFGHI
1Employee AJ 820123-07-5539Employee AJ23/1/1982WashingtonFState NoState
201Utah
302Texas
403Ohio
504New York
605California
706Wyoming
807Washington
908Florida
Employee Data

#### sythong

##### Active Member
Peter

Thank you for the suggestion.

Will try this too!!

Cheers

#### sythong

##### Active Member
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

##### Well-known Member
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

##### Active Member
You guys are extremely great
Thanks

Replies
5
Views
824
Replies
28
Views
553
Replies
9
Views
413
Replies
14
Views
539
Replies
0
Views
164

1,171,203
Messages
5,874,330
Members
433,044
Latest member
drewbizzy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back