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

1-4 =

1

2

3

4

See:

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

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

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

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.

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

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!

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 )

-=-=-=-=-=

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.