converting a string of information into excel cells
On Mon, 17 Apr 2006 10:02:36 -0700, H.W.
wrote:
Here in Texas our Drivers license has a mag stripe on the back with all the
D.L. information. I need to be able to swipe the D.L. an have the information
go into ExCell cells. Here is what I get when I swipe a D.L.:
%TXDALLAS^BLOW$JOE$DAN^123 SOMESTREET^?;63601512345678=060919740927?
(THIS IS ALL ONE LONG STRING. THE FORMATING HERE IN THE QUESTION BOX IS
SCREWING IT UP)
After the "%" is the state and city. Between the first "^" and second "^" is
lastname firstname midname with a "$" as the seperator. Between the second
"^" and the third "^" is their address. After the third "^" is "?;" then
their D.L. number until you get to the "=". The first four digits after the
"=" is the expiration date of their D.L. The last eight digits is their
birthday in the format YYYYMMDD. And then finially a "?" that ends the string.
Anyone have a good way to seperate this all out?
H.W.
Hi,
One way.
With your string in A1, (assumes the state is always 2 characters)
B1:=MID(A1,2,2)
C1:=MID(A1,4,FIND("^",A1)-4)
D1:=MID(A1,LEN(C1)+LEN(B1)+3,FIND("$",A1)-(LEN(C1)+LEN(B1)+3))
E1:=MID(A1,LEN(D1)+LEN(C1)+LEN(B1)+4,FIND("$",A1)-(LEN(D1)+LEN(C1)+LEN(B1)))
F1:=MID(A1,FIND("?",A1)-(LEN(E1)+LEN(D1)+LEN(C1)+2),FIND("?",A1)-FIND("?",A1)+LEN(E1)+LEN(D1)+LEN(C1)+1)
G1:=MID(A1,FIND("?",A1)+2,FIND("=",A1)-FIND("?",A1)-2)
H1:=MID(A1,FIND("=",A1)+1,4)
I1:=MID(A1,FIND("=",A1)+5,8)
HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
|