Number Range to List

[rivet]

Has anyone seen a calc or custom function for converting a number range into a return separated list?

1-4 =

1

2

3

4

[comment]

See:

http://fmforums.com/forum/showtopic.php?tid/191513/post/274473/#274473

[Robert Schaub]

See http://www.briandunning.com/cf/14

[Raybaudi]

...custom function for converting a number range into a return separated list?

What about this one ?

Range ( values )

Let([

sep = Filter ( values ; " -/|_*to" ) ;

start = GetAsNumber ( GetValue ( Substitute ( values ; sep ; ¶ ) ; 1 ) ) ;

stop = GetAsNumber ( GetValue ( Substitute ( values ; sep ; ¶ ) ; 2 ) )

];

Case(

stop - start ≥ 10000; "error" ;

start < stop ; start & ¶ & Range ( start + 1 & ¶ & stop );

start = stop ; stop

)

)

Note: values can be proposed in various ways, like:

1 to 4

1 4

1-4 ; 1 - 4

1/4 ; 1 / 4

14 ; 1 4

1|4 ; 1 | 4

1_4 ; 1 _ 4

1*4 ; 1 * 4

but must be both positive.

So this: Range ( "20 to 25" ) gives:

20

21

22

23

24

25

The CF can't handle range of more than 9999 values.

[rivet]

Thanks guys this works really well. Now can I take it one step further.

I would like the user to be able to type a range as well as individual numbers.

2-6, 8,11, 22-26

I need a CF:

1. return separate the list (sub "," ; "¶")

2-6

8

11

22-26

2. run through the list. I would assume 'MiddleValue'

3. if value contains "-" use 'EnumerateRange' else append to result

Recursive eludes me, any help is appreciated.

[comment]

I don't think this is a good method for data entry - too much room for user error. It would be better IMHO to enter the data into separate fields and records.

However, if you prefer, you can try it this way:

MultiRangeCF.fp7.zip

[rivet]

Thanks Comment, that is exactly it.

As for entry I agree with you, but this is for printing pages so it is temporary data.

Thanks Again!

[rivet]

hi Comment,

the left and right words had to be converted to numbers to work properly, I found this out when I tried "5-18"

-=-=-=-=-=-=-

MultiEnumerate

Let ( [

values = Substitute ( input ; [ "," ; ¶ ] ; [ "-" ; " " ] ) ;

countValues = ValueCount ( values ) ;

this = GetValue ( values ; 1 )

] ;

Case ( WordCount ( this ) > 1 ; Enumerate ( GetAsNumber ( LeftWords ( this ; 1 ) ) ; GetAsNumber ( RightWords ( this; 1 ) ) ) ; Trim ( this ) )

&

Case ( countValues > 1 ; ¶ & MultiEnumerate ( RightValues ( values ; countValues - 1 ) ) )

)

//MultiEnumerate ( input )

-=-=-=-=-=

[comment]

Actually, the fault is with the Enumerate () function, you should make the change there:

start & Case ( GetAsNumber (start) < GetAsNumber (end) ; ¶ & Enumerate ( start + 1 ; end ) )

Originally, when I wrote the Enumerate() function, the possibility of parameters being text never came up.