Just to confirm my understanding, is this the result you need for each of the lines you posted. The fifth line in particular?
CSA 10 ZN 6
CSA 10 ZN 6
CSA 39.2 ZN B2
Quite an interesting challenge. Have you checked it will not combine different funds to the same name or would that not matter anyway?
Are these records mixed up with others that do not start with a variation of the full CSA name? (In otherwords if we were to check whether a name started with CSA or not could we simply assume that any that did not start "CSA" could be changed to CSA?. Anything to the left of a # for example ...
First question, the format is correct, however on line 5 "CSA 39.2 ZN 2" would be the correct format, the "B" referred to Zone of Benefit. I thought it might be easier to use just numbers and throw out the rest for simplicity.
Question 2, all of the funds will have a unique numeric number that this description will attach to so if two of them end up having the same description that is OK. This is considered a "short fund name", there is another data field that has the long name with no abbreviations, although not in a consistent format either. (another project for another day)
Question 3, all of the funds start with some variation of CSA and if you noticed,(I am sure you did) one of the variations did not include a "#" before the CSA number.
My first thoughts were that somehow I could key off of the numbers using some variation of IF, IsAlpha to define the location in the string of the numbers and then pull those character locations to create a new string. ("CSA "+"(number)", etc). I know that I am going to run into difficulties with the variable zones. One piece of information that might help also is that none of the CSAs have a number higher than 99 but there are several that have either one or two trailing decimals.
I am able to go through the process in excel using find/replace several times to get to where I need to be, but I thought that there might be a way to clean it up through Monarch so that the next person that has to do this can simply use the models.
Thanks again for the help.
I think your first thoughts are very much on the right lines for a V6 approach.
There are some functions added in V7 that would make things a little easier and V8 has a REPLACE function that would make it much easier to normalize ZONE and "Z of B" to ZN!
However, I have a model developed with V6 for you to play with - there is a PM for you as well.