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
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.
- 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"
Feb 6, 2013
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?
Feb 7, 2013
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:
Hope that helps!! Enjoy!
Aug 24, 2016
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?
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.