3 in 1 extraction

sythong

Active Member
Joined
Jun 26, 2004
Messages
324
Hello again to me pals out there :biggrin:

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? :unsure:

Thanks to all who care to read this post!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
1009Alaska
Employee Data
 

sythong

Active Member
Joined
Jun 26, 2004
Messages
324

ADVERTISEMENT

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
Joined
Feb 28, 2003
Messages
2,615
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
 

Forum statistics

Threads
1,136,612
Messages
5,676,800
Members
419,651
Latest member
alexanderguhr

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top