> Malkie - this is very difficult to do with any high degree of reliability.
But what would be the simplest formula from word #2 (assuming the house number is word #1 which it is 99% of the time) until the "#" or if there are none, till the end of the field? > 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
