software

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 quot;%quot; is the state and city. Between the first quot;^quot; and second quot;^quot; is
lastname firstname midname with a quot;$quot; as the seperator. Between the second
quot;^quot; and the third quot;^quot; is their address. After the third quot;^quot; is quot;?;quot; then
their D.L. number until you get to the quot;=quot;. The first four digits after the
quot;=quot; is the expiration date of their D.L. The last eight digits is their
birthday in the format YYYYMMDD. And then finially a quot;?quot; that ends the string.

Anyone have a good way to seperate this all out?

H.W.
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 gt; TextToColumns gt; Delimited, using ^ as te delimiter and treating
consecutive delimiters as one........this will give you a good start.......

Vaya con Dios,
Chuck, CABGx3
quot;H.W.quot; wrote:

gt; Here in Texas our Drivers license has a mag stripe on the back with all the
gt; D.L. information. I need to be able to swipe the D.L. an have the information
gt; go into ExCell cells. Here is what I get when I swipe a D.L.:
gt; %TXDALLAS^BLOW$JOE$DAN^123 SOMESTREET^?;63601512345678=060919740927?
gt; (THIS IS ALL ONE LONG STRING. THE FORMATING HERE IN THE QUESTION BOX IS
gt; SCREWING IT UP)
gt;
gt; After the quot;%quot; is the state and city. Between the first quot;^quot; and second quot;^quot; is
gt; lastname firstname midname with a quot;$quot; as the seperator. Between the second
gt; quot;^quot; and the third quot;^quot; is their address. After the third quot;^quot; is quot;?;quot; then
gt; their D.L. number until you get to the quot;=quot;. The first four digits after the
gt; quot;=quot; is the expiration date of their D.L. The last eight digits is their
gt; birthday in the format YYYYMMDD. And then finially a quot;?quot; that ends the string.
gt;
gt; Anyone have a good way to seperate this all out?
gt;
gt; H.W.
gt;
gt;

On Mon, 17 Apr 2006 10:02:36 -0700, H.W.
gt; wrote:

gt;Here in Texas our Drivers license has a mag stripe on the back with all the
gt;D.L. information. I need to be able to swipe the D.L. an have the information
gt;go into ExCell cells. Here is what I get when I swipe a D.L.:
gt;%TXDALLAS^BLOW$JOE$DAN^123 SOMESTREET^?;63601512345678=060919740927?
gt;(THIS IS ALL ONE LONG STRING. THE FORMATING HERE IN THE QUESTION BOX IS
gt;SCREWING IT UP)
gt;
gt;After the quot;%quot; is the state and city. Between the first quot;^quot; and second quot;^quot; is
gt;lastname firstname midname with a quot;$quot; as the seperator. Between the second
gt;quot;^quot; and the third quot;^quot; is their address. After the third quot;^quot; is quot;?;quot; then
gt;their D.L. number until you get to the quot;=quot;. The first four digits after the
gt;quot;=quot; is the expiration date of their D.L. The last eight digits is their
gt;birthday in the format YYYYMMDD. And then finially a quot;?quot; that ends the string.
gt;
gt;Anyone have a good way to seperate this all out?
gt;
gt;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(quot;^quot;,A1)-4)
D1:=MID(A1,LEN(C1) LEN(B1) 3,FIND(quot;$quot;,A1)-(LEN(C1) LEN(B1) 3))
E1:=MID(A1,LEN(D1) LEN(C1) LEN(B1) 4,FIND(quot;$quot;,A1)-(LEN(D1) LEN(C1) LEN(B1)))
F1:=MID(A1,FIND(quot;?quot;,A1)-(LEN(E1) LEN(D1) LEN(C1) 2),FIND(quot;?quot;,A1)-FIND(quot;?quot;,A1) LEN(E1) LEN(D1) LEN(C1) 1)
G1:=MID(A1,FIND(quot;?quot;,A1) 2,FIND(quot;=quot;,A1)-FIND(quot;?quot;,A1)-2)
H1:=MID(A1,FIND(quot;=quot;,A1) 1,4)
I1:=MID(A1,FIND(quot;=quot;,A1) 5,8)

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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.

quot;Richard Buttreyquot; wrote:

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

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 quot;$quot; signs. I'm
assuming the name you want in this example is quot;Joe Danquot;, 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( quot;$quot;,SUBSTITUTE(A1,quot;$quot;,quot;quot;,1)) 1-(LEN(D1) LEN(C1) LEN(B1))),quot;$quot;,quot;quot;,1)

So I now see quot;JOE DANquot; 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(quot;^quot;,SUBSTITUTE(SUBSTITUTE(A1,quot;^quot;,quot;quot;,1 ),quot;^quot;,quot;quot;,2)) 2,FIND(quot;?quot;,SUBSTITUTE(SUBSTITUTE(A1,quot; ^quot;,quot;quot;,1),quot;^quot;,quot;quot;,2))-1-FIND(quot;^quot;,SUBSTITUTE(SUBSTITUTE(A1,quot;^quot;,quot;quot;,1),quot;^quot;,quot;quot; ,2)))

and I now see quot;123 SOMESTREETquot;

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, UKHere'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:=quot;~?quot;, Replacement:=quot;^quot;, LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=quot;=quot;, Replacement:=quot;^quot;, LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=quot;;quot;, Replacement:=quot;^quot;, LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=quot;$quot;, Replacement:=quot;^quot;, LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=quot;%quot;, Replacement:=quot;^quot;, LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=quot; quot;, Replacement:=quot;^quot;, LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=quot;^tquot;, Replacement:=quot;Tquot;, LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=quot;^^^quot;, Replacement:=quot;^quot;, LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Replace What:=quot;^^quot;, Replacement:=quot;^quot;, 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 _
:=quot;^quot;, 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 SubVaya con Dios,
Chuck, CABGx3

quot;H.W.quot; wrote:

gt; Here in Texas our Drivers license has a mag stripe on the back with all the
gt; D.L. information. I need to be able to swipe the D.L. an have the information
gt; go into ExCell cells. Here is what I get when I swipe a D.L.:
gt; %TXDALLAS^BLOW$JOE$DAN^123 SOMESTREET^?;63601512345678=060919740927?
gt; (THIS IS ALL ONE LONG STRING. THE FORMATING HERE IN THE QUESTION BOX IS
gt; SCREWING IT UP)
gt;
gt; After the quot;%quot; is the state and city. Between the first quot;^quot; and second quot;^quot; is
gt; lastname firstname midname with a quot;$quot; as the seperator. Between the second
gt; quot;^quot; and the third quot;^quot; is their address. After the third quot;^quot; is quot;?;quot; then
gt; their D.L. number until you get to the quot;=quot;. The first four digits after the
gt; quot;=quot; is the expiration date of their D.L. The last eight digits is their
gt; birthday in the format YYYYMMDD. And then finially a quot;?quot; that ends the string.
gt;
gt; Anyone have a good way to seperate this all out?
gt;
gt; H.W.
gt;
gt;