3 in 1 extraction

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,614

Office Version

1. 365

Platform

1. Windows

• Oct 5, 2006

• #2

Post deleted.

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Oct 5, 2006

• #3

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

 

Upvote 0

S

sythong

Active Member

Joined

Jun 26, 2004

Messages

324

• Oct 5, 2006

• #4

Krish

Nandrill.

I will work on it.

Best regards.

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

65,151

Office Version

1. 365

Platform

1. Windows

• Oct 5, 2006

• #5

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

 

Upvote 0

S

sythong

Active Member

Joined

Jun 26, 2004

Messages

324

• Oct 6, 2006

• #6

Peter

Thank you for the suggestion.

Will try this too!!

Cheers

 

Upvote 0

S

sythong

Active Member

Joined

Jun 26, 2004

Messages

324

• Oct 8, 2006

• #7

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

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Oct 9, 2006

• #8

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

 

Upvote 0

S

sythong

Active Member

Joined

Jun 26, 2004

Messages

324

• Oct 9, 2006

• #9

You guys are extremely great
Thanks

 

Upvote 0