Sage 100

Has anyone used Providex functions to extract a ""City, St Zip"" string into the three separate fields used in Sage when importing with Visual Integrator?

I used the MID() function to extract a portion of a string, however this begs the question of whether the City, St, Zip are fixed length or variable for which you'd have to figure out the length before/while using MID()

I'm thinking I will need a function to find the comma, then pull the city from the characters before the comma. After that, take the next two characters after the comma as the state. Finally, take the last characters after the state as the zip code. It looks like a couple hours of work for me, unless I can take advantage of someone else's work.

Not in one line, but I have used this to satisfaction.... Perform logic? IF csz$<>"""" THEN { LET tmp$=STP(csz$,2) ! Strip external spaces LET tmp$=STP(tmp$,3,"","") ! Strip out commas LET tmp=POS("" ""=tmp$,-1) ! Parse zip IF tmp THEN { LET zip$=STP(mid(tmp$,tmp+1),2) LET tmp$=STP(mid(tmp$,1,tmp-1),1) LET tmp=POS("" ""=tmp$,-1) ! Parse state IF tmp THEN { LET state$=UCS(STP(mid(tmp$,tmp+1))) ! Strip Spaces & make uppercase LET city$=STP(mid(tmp$,1,tmp-1),1) } ELSE { LET city$=tmp$ } } ELSE { LET city$=tmp$ } }

City$=STP(MID(IMP$,1,POS("",""=IMP$)-1),2); Temp$=STP(MID(IMP$,POS("",""=IMP$)+1),2); State$=MID(Temp$,1,POS("" ""=Temp$)-1); Zip$=STP(MID(Temp$,POS("" ""=Temp$)+1),2) I am doing this off the top of my head. It will work, as long as the comma is there and as long as the state and zip are separated by space. The STP() function with the ""2"" option is to strip leading and trailing spaced.

I designed the function to work with or without commas and deal with multi word cities.... Makes it a little long

@RandyMarion, I saw that you're was a more robust set of code, that's why I made sure to specify that my little string assumed a comma. In the interest of taking this way farther than anyone is interested in, it occurred to me that you could start from the end of the string and work backwards and come up with something that handles with or without comma: Zip$=STP(MID(IMP$,POS("" ""=IMP$,-1)),2); IMP$=STP(MID(SUB(UCS(IMP$),"","","" ""),1,POS("" ""=IMP$,-1)),2);State$=STP(MID(IMP$,POS("" ""=IMP$,-1)),2);City$=STP(MID(IMP$,1,POS("" ""=IMP$,-1)),2)

Thanks to all of you! However, now my client thinks they can get the fields separated in the file after all. But I am going to copy these notes for future reference. Thanks again.

@PhilipWhirley - Yes, working backward from the string is really the only way to deal with multi-word cities, strip off the zip and the state and whatever is left, must be the city!