Malkie - this is very difficult to do with any high degree of reliability. It is fairly straight forward to extract the street number. You look for the first blank space with the character that precedes it being a number. Since some of my addresses have no number the formula returns a blank. This forumla searches for street numbers that are up to five digits long.
if substring[P4F2;2;1] = " " and substring[P4F2;1;1] >= "0" and substring[P4F2;1;1] <= "9" then substring[P4F2;1;1] else if substring[P4F2;3;1] = " " and substring[P4F2;2;1] >= "0" and substring[P4F2;2;1] <= "9" then substring[P4F2;1;2] else if substring[P4F2;4;1] = " " and substring[P4F2;3;1] >= "0" and substring[P4F2;3;1] <= "9" then substring[P4F2;1;3] else if substring[P4F2;5;1] = " " and substring[P4F2;4;1] >= "0" and substring[P4F2;4;1] <= "9" then substring[P4F2;1;4] else if substring[P4F2;6;1] = " " and substring[P4F2;5;1] >= "0" and substring[P4F2;5;1] <= "9" then substring[P4F2;1;5] else " " endif endif endif endif endif You can use the same formula to extract the remainder of the address by adjusting the substring argument to capture everything to the right of the blank space rather than everything in front of it. Pulling a suite or apartment number out of what is remaining depends on the reliability of how the data was entered. If it always reads " . . . APT #xxx" then it's easy by searching for the APT text but if it is random in nature it is pretty impossible to build a forumla. Good luck. Don On Sun, Jan 18, 2009 at 1:29 PM, <[email protected]> wrote: > P1F7 is the address only, like: 1234 E. 4 St., or 1234 Park Hts Ave. > #3C or 205 Main Street, Suite #201 or any amount of words. Cities > and States have separate fields. > > > Malkie - how about posting three examples of the addresses as they > > exist in the database. I've done this kind of thing frequently but at > > least for addresses I deal with it is prone with problems. For > > example, if all addresses read like: > > > > >>>>> 205 Main St, Pittsburgh, PA, 15215 <<<<<< then it's pretty easy. > > >>>>> But > > if the next address reads like > > >>>>>>205 Main St, Suite 201, Pittsburgh, PA, 15215 <<<<<< then it > > >>>>>>gets more > > difficult. > > > > I often find that starting at the end and working backwards works more > > efficiently. > > > > Don > > > > On Sun, Jan 18, 2009 at 1:13 PM, <[email protected]> wrote: > > > > > Thank you Robert for your response. I've copied your entire formula > > > into that field and replaced your P1F7 by selecting the actual field > > > with F4. For some reason I keep getting the message: "incorrect > > > number of arguments for function" pointing after the first > > > "length[substring[P1F7]]". I cannot figure out what this means. Are > > > you sure this is the correct formula? > > > > > > And regarding your question, there's always a space before the "#" > > > and there might or might not be any commas beforehand. I've no idea > > > what my client entered into that field. If it's important I can > > > check. I don't think this should make a difference, though. > > > > > > > since sending the formula, i realize that i didn't think about - > > > > not did you mention, whether your existing data has a comma and > > > > space preceding thin coming > > > > > > > > On Sun, Jan 18, 2009 at 11:50 AM, Robert Pollard > > > > <[email protected]>wrote: > > > > > > > > > malkie > > > > > > > > > > here is one way that might work, although there may be simpler > > > > > ones > > > > > > > > > > if contains[P1F7;"#"] then substring[P1F7; > > > > > length[substring[P1F7]]+2; length[subfield[P1F7;"#";1]] - > > > > > length[substring[P1F7]] -2] else substring[P1F7; > > > > > length[substring[P1F7]]+2; length[P1F7] - > > > > > length[substring[P1F7]] -2] endif > > > > > > > > > > hth > > > > > > > > > > robert > > > > > > > > > > > > > > > On Sun, Jan 18, 2009 at 7:21 AM, <[email protected]> > > > > > wrote: > > > > > > > > > >> I would like to divide an address field of 50 characters into 3 > > > > >> (a) house number; (b) apartment number; (c) everything in > > > > >> between > > > > >> > > > > >> (a) field has the formula: subfield[P1F7;' ';1] > > > > >> (b) field has the formula: If contains[P1F7;"*#*"] then > > > > >> "#"subfield[P1F7;"#";2] else " " endif > > > > >> What's the formula for (c) field? > > > > >> > > > > >> Malkie > > > > >> > > > > >> _______________________________________________ > > > > >> Dataperf mailing list > > > > >> [email protected] > > > > >> http://lists.dataperfect.nl/mailman/listinfo/dataperf > > > > >> > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > Dataperf mailing list > > > [email protected] > > > http://lists.dataperfect.nl/mailman/listinfo/dataperf > > > > > > > > > > > -- > > Don Friedman > > ProfessionalRecords.Com LLC > > PRS Data Systems > > 205 S Main Street > > Pittsburgh, PA 15215 > > 412-784-1600 - 1-800-PRS-FILE > > 412-784-1615 Fax > > > > > _______________________________________________ > Dataperf mailing list > [email protected] > http://lists.dataperfect.nl/mailman/listinfo/dataperf > -- Don Friedman ProfessionalRecords.Com LLC PRS Data Systems 205 S Main Street Pittsburgh, PA 15215 412-784-1600 - 1-800-PRS-FILE 412-784-1615 Fax
_______________________________________________ Dataperf mailing list [email protected] http://lists.dataperfect.nl/mailman/listinfo/dataperf
