You could use a formula which checks for a space in the code and if there is no space use the first 4 space and last 3
if(patterncount(code;" ")=0 ; left(code;4)&" "& middle(code;5;3) ; code)
Hope this helps,
Ruben van den Boogaard
Thank you very much. 1.6M records now in a consistent format.
Jeremy Murfitt FRICS FAAV FIQ
everything is somewhere
Manor Cottage, 60 Main St, West Leake
Leics, LE12 5RF
O: 01509 853792
M: 07802 622923
PLEASE NOTE NEW EMAIL ADDRESS
I will keep monitoring my "btinternet" address but please update your contacts in the meantime.
Are you creating a new field with this consistent format? Another way to do it would be to have a script loop through all of the records to fix the data in that field. That way, you won't have 1.6 million fields of basically, duplicated data.
Going forward, you could also set up some formatting rules that force users to enter the data in the consistent format, and/or fix it for them automagically with a script trigger.
A better way than trying to force users to enter data in a particular way (because this necessitates making sure they know the rules, and dealing directly with them if they don't follow the rules) is to set the field to replace whatever text is entered by the user with a formatted version of itself, using the autoenter by calculation function in the field setup, set to always replace.
Yes or use the trim function to remove all spaces and then put the space back into all of them. Either way is fine. For me I just like forcing a consistent string in this case no spaces and then once you have that do what ever you want to it.