Brian Dunning's FileMaker Custom Functions

NumberRange_Explode ( numberRangeText )

Receives a string of numbers separated by commas, hyphens, or any multiple or combination or both, and returns a return delimited list of all whole numbers within the input ranges

  Average rating: 4.4 (29 votes) Log in to vote

Jonathan Mickelson   Jonathan Mickelson

Share on Facebook Share on Twitter

  Sample input:
NumberRange_Explode ( "1-3,5,7-10" )
  Sample output:
"1¶2¶3¶5¶7¶8¶9¶10"

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

This takes a number string either as commas (1,5,8) or a hyphen (2-11), or any combination of the two (2,7,10-14,21,50-62,72), and returns a return delimited list of all whole numbers included within the given ranges.

This is very useful in building multi-line keys for relationships, or in conjunction with other methods, such as substitute lists or the awesome CustomList ( ) function.

NOTE: hyphenated ranges can be given in any order (2-5 or 5-2), results are ordered from smallest number to largest, regardless or original order.

Examples:

- NumberRange_Explode ( "1-3" ) = "1¶2¶3"
- NumberRange_Explode ( "2,4,8,11" ) = "2¶4¶8¶11"
- NumberRange_Explode ( "1-3,5,7-10" ) = "1¶2¶3¶5¶7¶8¶9¶10"
- NumberRange_Explode ( "20-16" ) = "16¶17¶18¶19¶20"

 

Comments

Adam   Adam, Melbojrne
Feb 6, 2013
Hi,

I'm very excited to see this, I've been struggling to find this function for awaile. Unfortunately I'm a bit of a newbie so I'm not able to get it to work. Would it be possible to post a demo?

Cheers
Adam
 
Jonathan Mickelson   Jonathan Mickelson
Feb 7, 2013
Hi Adam,

I've tried re-pasting the text form a working version, just in case something was wrong in the previous pasted version. This works and is in production for me.

This is a recursive function, so it must be made into a Custom Function to work, since it must call itself until it is finished.

I'm not sure what part is tripping it up for you, but here's a brief step by step:

- Open FileMaker Pro Advanced, to add a new Custom Function.
- Copy and paste the text within the calc window above into it.
- Edit the Function name and parameters to match the Comment at the top of the calc text.

When you close the dialog it should validate the calc, and close correctly. You may now use the function. I'd suggest opening the Data Viewer, and pasting the following into the calc to test it:

NumberRange_Explode ( "1-3,5,7-10" ) = "1¶2¶3¶5¶7¶8¶9¶10"

You should see the result in the results area below and it will look like this:

1
2
3
5
7
8
9
10

Hope that helps!! Enjoy!
Jonathan
 
LG   LG, LA
Aug 24, 2016
Hi,

I'm looking for something that does the opposite of this -- where the input would be a list of numbers and the output would be a number range as text.

So something like:
NumberRange_Create ( "1¶2¶3¶5¶7¶8¶9¶10" ) = "1-3, 5, 7-10"

I've attempted to wrap my mind around building this with a custom function but it's beyond my training. Any ideas?

Many thanks
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.