Formula to Find Sequance/Range

[Robert_Fabian]

​i want the sequence from Filename column to appear as range in the next column

is there any formula to do this
i need a formula to get the highlighted values from the Filename Column

[TABLE="class: outer_border, width: 1007"]
<tbody>[TR]
[TD] Date[/TD]
[TD]Box.no[/TD]
[TD="align: center"]Filename[/TD]
[TD]Range[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2014[/TD]
[TD="align: right"]1000[/TD]
[TD="align: center"]5000132355[/TD]
[TD]5000132355-5000132363[/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132356[/TD]
[TD] 50001032556[/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132357[/TD]
[TD] 5000132364-5000132385[/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132358[/TD]
[TD] 5000125322[/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132359[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132360[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132361[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132362[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132363[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5001032556[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132364[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132365[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132366[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132367[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132368[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132369[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132370[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132371[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132372[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132373[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132374[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132375[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132376[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132377[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132378[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132379[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132380[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132381[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132382[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132383[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132384[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132385[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000125322[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132386[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132387[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132388[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132389[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132390[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132391[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132392[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132393[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132394[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132395[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132396[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132397[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132398[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132399[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132400[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132401[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132402[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132403[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132404[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132405[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132406[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132407[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132408[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132409[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132410[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]5000132411[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

5000132363 is the value i intended to be the last number of the sequence

How is Excel going to know that?

 

[Kamolga]

We need a rule to establish beginning and end of sequence like the first 9 identical digits set up sequences, the lowest 10th is the beginning and the largest 10th is the end.

The question is it based on what? With the above table, there is no way for a human (anyone but you) or Excel to know if this is beginning,middle or end of sequence...so it has to be done manually by you.

 

[Robert_Fabian]

Ok forget everything now what i need in that RANGE Column is the range of sequence of Data of Filename Column
Note:Even if one number in the sequence is Missing the next Sequence Should Start
if there are numbers which does not appear in any of the sequence it should be entered seperate