Hi,

 

This is clean data issue.

I obtain from and old database field, called "num" where the users put the street number or gate number, these 13 values from the index(:

 

 

12

13

14

14 16

14 Y 16

14-16

14-16-18

14,16,18

14y16

15

16

17

17y20

 

and in order to obtain a clean relationship data I have to transform in

 

12

13

14

14¶16

14¶16

14¶16

14¶16¶18

14¶16¶18

14¶16

15

16

17

17¶20

 

As you may imagine I need this kind of data in the child side of the relationship just to see  #14 related issues in one instance and not in 6 different cases. I have been browsing BDunning and FMFunctions with no luck cause I guess that a custom function should fit perfectly. Any suggestions??

 

Thanks in advance,

Hi Jorge,

 

Calculation ( result is text ) with the following:

 

Substitute ( Lower ( YourField ) ;
[ " y " ; ¶ ] ;
[ "y" ; ¶ ] ;
[ "-" ; ¶ ] ;
[ "," ; ¶ ] ;
[ " " ; ¶ ]
)

 

If I've misunderstood your need, let me know.

Hi LaRetta,

 

no you haven´t  

 

But the data shown is only a small set of "free text" that after 37 thousand records -18 thousand with data in this field- it is a bit more complicated that your precise calc.

I have all sort of not numeric data inside the field. My approach should be how to filter the numbers but not loosing the value or the word to be understood. 

 

I also have some text data (a few hundred good ones) with "backside", "lower", "bis", "prox", that I will try to save after cleaning numbers. The point as you'll guess is to construct a normalized data field, leaving the old one

 

the calc should, I guess:

 

 

- replace not numeric data with spaces

- trim

- replace the last spaces with ¶

 

If I am misfocused please comment me. 

 

Thanks, LaRetta, 

Ah, well you said, "these 13 values from the index" so I assumed it was an example of each possibility.  Try this (attached) custom function.  I think it will do the job for you.

 

ADDED:  Let us know if you get stuck on the rest of the parsing of the "backside", "lower" etc.  :^)

parse.zip

Edited February 26, 201312 yr by LaRetta

Hi LaRetta,

 

Thank you very much for this strip CF. It works gorgeously, as asked. 

After setting it up I will deal with the commented exceptions. 

 

Best of all!!