splitting field containing address

[DWag]

In our ERP database, all lines of customer addresses are stored in one field, with 4 lines possible. Example of how data is stored in the field:

1234 West;;;
1234 west; P O Box 556;;
1234 west; P O Box 556; suite 1234;
1234 west; P O Box 556; suite 1234; floor 12

With the number of lines per address per customer varying, how do I split this one field into 4 separate fields? I was able to get the first line with:

Left({addressfield},Instr({addressfield},";&quot-1)

But am now stuck.

TIA for any suggestions!

 

[mbarron]

You can do this using the Split() function.

Create 4 formulas to display each part of the address.

formula 1
split({@address},";&quot[1]

formula 2
split({@address},";&quot[2]

formula 3
split({@address},";&quot[3]

formula 4
split({@address},";&quot[4]



Mike

 

[synapsevampire]

Try a formula for each containing:

whileprintingrecords;
stringvar array Addresses :=split({addressfield},";"
Addresses[1]

Just increment the [1] for each address.

-k

 

[DWag]

Thanks for the quick responses! Used mbarron suggestion, works like a charm! I had tried using split but was using it like this:

split({@address},";",1) instead of [1]

 

[synapsevampire]

They're the same formula, I just fleshed out the formula into components.

-k