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 - Show more from this author

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)

// NumberRange_Explode ( numberRangeText )

Let ( 
[ 
input = numberRangeText ; 

posComma = Position ( input ; "," ; 0 ; 1 ) ; 

   parse = Case ( posComma > 0 ; Middle ( input ; 0 ; posComma - 1 ) ; input ) ; 
   remainRange = Case ( posComma > 0 ; Middle ( input ; posComma + 1; 1000 ) ; "" ) ; 


posDash = Position ( parse ; "-" ; 0 ; 1 ) ; 

   start = GetAsNumber ( Case ( posDash > 0 ; Middle ( parse ; 0 ; posDash - 1 ) ; parse ) ) ;  
   end = GetAsNumber ( Case ( posDash > 0 ; Middle ( parse ; posDash + 1 ; 1000 ) ; parse ) ) ; 

from = Case ( start > end ; end ; start ) ;   //  Allows for bidirectional range input
to = Case ( end < start ; start ; end ) ;       //  Allows for bidirectional range input

nextRange = Case ( to > from ; from + 1 & "-" & to & "," ) & remainRange ; 

result = from

] ; 
// CALC TEXT BELOW =================================

result &

Case (// DETERMINE IF MORE IS NEEDED AND RECURSE FURTHER
         not IsEmpty ( nextRange ) ; 
         ¶ & NumberRange_Explode ( nextRange ) 
         )

) // END LET

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, 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 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, 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

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

NumberRange_Explode ( numberRangeText )

Comments

Support this website.

Support this website.