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
