Split numerical vales joinned by && into individual rows

J

JaneBrown

I need to split numerical vales joined by && into their individual rows, e.g.
1030&&1034 into 5 rows with all numbers in the range on their own row
 
R

Ron Coderre

You might be able to use this:

With your list of ranges in Col A
A1: List
A2: 1030&&1035
A3: 1040&&1050
A4: 1062&&1075
etc

D1: RangeRef
E1: Ref
F1: RangeCount
G1: RangeStart
H1: RangeItem
I1: Number

D2: =IF(H1=F1,D1+1,MAX(N(D1),1))
E2: =OFFSET($A$1,D2,,,)
F2: =MID(E2,FIND("&&",E2)+2,255)-LEFT(E2,FIND("&&",E2)-1)+1
G2: =--LEFT(E2,FIND("&&",E2)-1)
H2: =IF(G2<>G1,1,G1+1)
I2: =G2+H2-1

Copy the formulas in Cells D2 through I2 down as many rows as you need.

Not especially elegant, but does that help?

••••••••••
Regards,
Ron
 
Top