Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting a string of information into excel cells
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting a string of information into excel cells
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 "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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting a string of information into excel cells
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 Buttrey" wrote: 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 __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting a string of information into excel cells
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting a string of information into excel cells
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 "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Entering Excel information into MS Word | Excel Discussion (Misc queries) | |||
How can I have excel search and add multiple cells to find a targe | Excel Discussion (Misc queries) | |||
Excel - counting cells containing a text string | Excel Worksheet Functions | |||
Extract hyperlink string from excel cell | Links and Linking in Excel |