converting a string of information into excel cells

[H.W.]

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.

 

[CLR]

Just highlighting the column and going through one at a time and doing
Find/Replace on all the wild characters and replacing them all with the ^
then Data > TextToColumns > Delimited, using ^ as te delimiter and treating
consecutive delimiters as one........this will give you a good start.......

Vaya con Dios,
Chuck, CABGx3

 

[Richard Buttrey]

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
__________________________

 

[H.W.]

Richard, Thank You ! Most of this works. I'm totally lost when it comes to
something like this. There seems to be something missing between D1 and F1. I
only get part of the first name in cell E1. Then part of the middle name and
the address in F1. I've checked my typing but can't find a problem. Am I
putting something in wrong????

Thanks again,
H.W.

 

[Richard]

Hi,

Ah, OK, I can see a problem with E1 in that it was only returning the
first part of the name before the first and second "$" signs. I'm
assuming the name you want in this example is "Joe Dan", and I've
assumed a space between Joe and Dan. If this is what you need then
please modify E1 to be:

=SUBSTITUTE(MID(A1,LEN(D1)+LEN(C1)+LEN(B1)+4,FIND("$",SUBSTITUTE(A1,"$","",1))+1-(LEN(D1)+LEN(C1)+LEN(B1))),"$","",1)

So I now see "JOE DAN" in E1

I couldn't see a problem with the original F1, although now having
corrected E1, there's a knock on effect on F1 which should be changed
to:

=MID(A1,FIND("^",SUBSTITUTE(SUBSTITUTE(A1,"^","",1),"^","",2))+2,FIND("?",SUBSTITUTE(SUBSTITUTE(A1,"^","",1),"^","",2))-1-FIND("^",SUBSTITUTE(SUBSTITUTE(A1,"^","",1),"^","",2)))

and I now see "123 SOMESTREET"

If it still doesn't evaluate correctly, please post back.

I suspect there may be a more elegant way of simplifying these string
functions. so I'll have another think. If you're happy with running a
VBA macro, then that would be one other option, and probably only need
a few lines of code. Let me know

Kind regards,

Richard Buttrey
Grappenhall, Cheshire, UK

 

[CLR]

Here's a macro that should do it all in one fell swoop.........
Watch out for the word-wrap when copy/paste

Sub TexasDriversLicense()
' Separates data from Texas Drivers License string
' With string in cell in column A, highlight cell and run macro
' Note: needs 7 empty columns to the right of highlighted cell
ActiveCell.Select
Selection.Replace What:="~?", Replacement:="^", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:="=", Replacement:="^", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=";", Replacement:="^", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:="$", Replacement:="^", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:="%", Replacement:="^", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=" ", Replacement:="^", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:="^t", Replacement:="T", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:="^^^", Replacement:="^", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:="^^", Replacement:="^", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="^", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2),
Array(4, 2), Array(5, _
2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2))
End Sub


Vaya con Dios,
Chuck, CABGx3