In computer terms, Consistency Is Next To Godliness. What you have here is not a problem, if the addresses were all consistent in the placing of their components and commas.
Your first decision, I think, is whether to try and produce consistency before you try and parse them, or parse them and then try and sort the anomalies.
You can use the Position function to determine where each comma is (you can find the first instance, the second instance, etc).
You can use the Middle, Left, or Right functions to extract characters to or from the comma position, say.
You can use the Trim function to strip off any leading or traling spaces on the result.
Combining those functions will extract the parts of that full address, but as you say if the third comma-to-comma section sometimes contains the Estate and sometimes the Town you will have to amend those manually. (Unless, for example, you have 'second level consistency', and whenever something is missing it is always the Estate.)
Your next decision is whether to leave that Full Address field as-is, and create 5 calculations (one for each component) from it. If you are consistent from now on, that will work, but I wouldn't recommend it. It is always easier to enter smaller bits of data and combine them, than split them out later.
I would recommend that you create 5 more calculation fields (one for each component) and then perfect each calculation. Then copy that proven calculation, change the field to be a text field, then use the Replace Field Contents menu option to replace (via calculation - that copied calculation) into the field. Repeat for the other components. Then you have full access to edit and fix any anomolies, and from now on you should enter the components directly into those component fields. When you have all addresses purged and corrected, you can change the Full Address field to be a calculation field, and then build it up from the separated components.
Make a backup first!
I understand what you are saying...sort of..ha......but my problem is as I'm still new to calculations and scripts I don't really know the correct way to write them out.
So if Full Address field = 1, My Street, Town, County would I then put a calculation in each of the fields below?
And my other problem is writing out the calculations. Do I use position and left/middle/right in the same calculation? How do I combine them, etc. I'm really sorry but these things still really confuse me.
Here's an alternative to using the position function:
Substitute ( AddressField ; "," ; ¶ )
this converts your comma separated list into a return separated list and you can use the GetValue function to extract specific values from this list.
Set Variable [ $AddressList ; value: Substitute ( YourTable::AddressField ; "," ; ¶ ) ]
SetField [YourTable::Address1 ; GetValue ( $AddressList ; 1 ) ]
For example will extract everything to the left of the first comma and put it in Address1.
Learned this trick from Raybaudi.
But as Sorbsbuster indicated, a lack of consistancy could make your efforts to parse out the data from this one field less than perfect. THe second value in your list could be data for Address2 or it could be data for Town.
THanks very much Phil that worked the best for me! I did have to fix some of them but it was rare.
Is there an alteration to that so that it removes any spaces between the lines? At the moment when I use the substitue function to get them in lines from the second line onwards there is a space at the beginning of the line.
You might try using the Trim function:
SetField [YourTable::Address1 ; Trim ( GetValue ( $AddressList ; 1 ) ) ]
Thanks Phil! I was trying it in the Substitue part and it wasn't working