need to break up string of numbers with commas

[alphanitrate]

I need to take a field with the following content and break it up into a group of 7 digit item numbers.

Recommended Accessories:

* 8264608 - Hose

* 8041543 - Holder

* 8258600 - Accessories kit

* 8001938 - Grease

I have found this entry which was helpful in removing all the text:

http://www.fmforums.com/forum/showtopicforreply.php?iframe/1/fid/36/tid/210907/pid/339589/

I now have a field with: 826460880415438258600 8001938.

I would like to have the field formatted like this at the end: 8264608,8041543,8258600,8001938

So I have two questions:

This field may have no items or multiple items, and may or not contain other numbers.

1.How do I get it to look for only 7 digit numbers out of that field?

2. How do I add in a comma after every 7th number?

This is done in Filemaker 8.5

[Genx]

Use the original field contents instead of your filtered contents. Filter for all numbers and pilcrows (line delimiters). Substitute the delimiters for commas.

Substitute(

Filter("* 8264608 - Hose

* 8041543 - Holder

* 8258600 - Accessories kit

* 8001938 - Grease" ; "0123456789¶")

;¶;"," )

** Obviously replace

"* 8264608 - Hose

* 8041543 - Holder

* 8258600 - Accessories kit

* 8001938 - Grease" with your actual field.

Edited October 1, 2009 by Guest

[alphanitrate]

First of all let me say that you're awesome - this makes it way simpler.

However do to some thing on my end, I'm still having issues. Heres what I am using:

Substitute(

Filter(${LDTEXT -Main} ; "0123456789¶")

;¶;"," )

RESULT:

8264608804154382586008001938

I'm missing something...

Thanks again for your help.

[comment]

Could you post a sample file showing this? Your result indicates there are no carriage returns separating the values - contrary to the example in your first post.

I am also puzzled by your requirement "to look for only 7 digit numbers out of that field". Your example doesn't have any numbers other than the 7-digit numbers in front of each value. But if you expect input like:

* 1234567 - Pipe, 3/4"

* 2345678 - Wine, 1956

then this won't work.

[Lee Smith]

comment has made a good point about the data.

As for you calculation, try;

Substitute ( Filter ( Recommended Accessories ; "0123456789¶" ) ; ¶ ; ", " )

[alphanitrate]

Thank you all for all you assistance. Here are two files that might clear up what I am trying to do.

AS400 Screen - this is a screen shot of the screen as they set it up in the AS400 system we have. (We have to use this for various reasons, otherwise i could eliminate it.)

alphanitrate2.xls this has 3 columns SKU, MAIN COPY (this is raw data) and Recommended (which is the filtered results)

as you can see the filtered results are almost perfect, however due to the multiple skua, i need to place a comma after every seventh digit. regardless of number length.

As for the "only look for 7 digit numbers" some of the records will have other numbers in their records. IE: 1234567–500lbs etc.

This should return a result of 1234567500 which is incorrect.

I don't know if what i am asking is impossible or not, I'm still so new to this.

Alphanitrate.zip

[comment]

You should really use a custom function for this. In the absence of the Developer version, you could use a script instead, or a repeating calculation field =

Let ( [

cln = Filter ( Extend ( YourField ) ; "0123456789¶" ) ;

i = Get ( CalculationRepetitionNumber )

] ;

Left ( GetValue ( cln ; i ) ; 7 )

)

Set the number of repetitions to the maximum expected accessories.

If you wish, you can then use another calculation field to convert the results to a comma-separated list =

Substitute ( List ( cRepeatingCalc ) ; ¶ ; ", " )

However, I am not sure what purpose is served by that - it seems that these numbers should either remain separated by a carriage return (or in individual repetitions) in order to enable a relationship, or they should go into separate records in a related table.

[alphanitrate]

I hate to ask, but where do I enter the number of repetitions?

[comment]

Below the formula, next to the result type.