| Author |
Message |
newbie1967
Newbie
Joined: 27 Aug 2012
Online Status: Offline
Posts: 3
|
  
Topic: How to seperate an address string
Posted: 27 Aug 2012 at 11:16am |
Hello,
I am new at this and I am having difficulty seperating an address string into seperate lines. All I need to do is to get the city, state and zip code pulled out of each address. The issue I am running into is that not each address string is the same 
i.e:
788 Research Street
Attn : Bob
Rockford IL 61109
USA
70526 N Milwaukee Ave
Hills IL 60061
jill 315-437-8526
The city state and zip are not always in the same line in the string.
Please HELP!!!  |
 IP Logged |
|
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Aug 2012 at 11:25am |
|
The only thing I can think of is parsing with the second line and going until you find a five digit number. I think the code would be fairly complicated.
|
| IP Logged |
|
newbie1967
Newbie
Joined: 27 Aug 2012
Online Status: Offline
Posts: 3
|
Posted: 27 Aug 2012 at 11:58am |
That was my thought also, but I could not figure out the formula and kept getting errors.
The only other issue is that some of the address portion of the string have a 5 digit number and I think it might pull those instead of the zip when appicable.
12896 first hanger rd
villa park, il 60528
Can anyone help me with the coding?
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Aug 2012 at 12:20pm |
|
If I had some sample data set up, I might be able to come up with a solution. The first thing to do (with a string shared variable) is to strip out the first line, since that would be the street address (PO Box?). Then have a loop of some sorts to look for a numeric character and then get the next five characters (provided they are numeric).
I hope this helps.
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Aug 2012 at 1:03pm |
|
I made some sample data and here is what I came up with. It assumes a lot (virtually no error checking). I thought about switching to BASIC syntax since it works with strings better. Lots of luck.
shared stringvar addr;
local numbervar i :=1;
local numbervar j;
local stringvar zip;
addr := right({address_tbl.address},len({address_tbl.address})-instr({address_tbl.address},chr(13))-1);
while i <= len(addr) Do
(
if isnumeric(mid(addr,i,1)) then
(
for j := i to i+4 do
(
zip := zip + mid(addr,j,1);
);
zip;
exit while
);
i := i +1;
);
zip
|
| IP Logged |
|
newbie1967
Newbie
Joined: 27 Aug 2012
Online Status: Offline
Posts: 3
|
Posted: 28 Aug 2012 at 4:11am |
It worked for some but not all of the strings.
ROCKFORD WAREHOUSE
250 QUAKER RD
ROCKFORD IL 61104
USA
For this address string it pulled 50 Q
Im not sure why it is doing this
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 28 Aug 2012 at 5:08am |
|
Because the examples you gave me starts the address on the first line. Like I said, there is no error checking. It would take some thought how to skip that second line.
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 28 Aug 2012 at 6:35am |
|
I put some other checks in. Hopefully it will work correctly for you. It is now in Basic Syntax, so be sure to change the formula editor to Basic Syntax.
dim addr as string
dim i, j as number
dim zip as string
addr = right({address_tbl.address},len({address_tbl.address})-instr({address_tbl.address},chr(13))-1)
for i= 1 to len(addr)
if isnumeric(mid(addr,1,1)) then
addr = right(addr, len(addr)-1)
else
exit for
end if
next i
for i= 1 to len(addr)
if isnumeric(mid(addr,i,1)) then
if isnumeric(mid(addr,i+4,1)) then
for j = i to i+4
zip = zip + mid(addr,j,1)
next j
exit for
end if
end if
next i
formula = zip
|
| IP Logged |
|
|
|
