Looking for consectuive numbers bewteen hypen

[Brawnystaff]

I am looking to get all number combinations for numbers stored in a column as text, with some of the number separated by a hypen to abbreviate a number range (e.g. 1-3 would be 1,2,3), and some just singles numbers (e.g. 6).

Attached is a sample workbook, with data I am starting with in Sheet "Before" and the results in sheet "After". Any ideas on how to do it? Thanks.

[nilem]

Hi Brawnystaff,
try this

Please Login or Register  to view this content.

[patel45]

Please Login or Register  to view this content.

[XOR LX]

Horribly inefficient as it is compared to the two VBA offerings, if you're after a formula-based solution then first go to Name Manager and define a new name, Arry1, as:

=MMULT(0+(ROW($A$1:$A$3)>=TRANSPOSE(ROW($A$1:$A$3))),1+MMULT(IFERROR(0+TRIM(MID(SUBSTITUTE($A$1:$A$3,"-",REPT(" ",33)),33*{0,1}+1,33)),0+$A$1:$A$3),{-1;1}))

(Amend the range ($A$1:$A$3) as required, though make sure it is an absolute reference (the "dollar signs"), as here.)

The array formula** is then:

=IFERROR(SMALL(ROW(INDIRECT(SUBSTITUTE(INDEX("A"&SUBSTITUTE($A$1:$A$3,"-","-A"),MATCH(TRUE,Arry1>=ROWS($1:1),0)),"-",":"))),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1))),"")

Copy down until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).