Convert list of alpha numeric into ranges

Ok try this.

in C1
=IFERROR(IF(RIGHT(B1,9)+1=RIGHT(A2,9)+0,1,0),0)
and copy this down the column
(This helper column is an indicator of whether the next row is contiguous or not).


in D1
=A1

in D2
=IF(C1=1,D1,A2)
and copy this down the column
(This helper column is either the start of a range copied down or the start of a new range)

in E1
=IF(C1=1,"",B1)
and copy this down the column
(This helper column is either a blank if contiguous or the end of a contiguous range)

Now place an Autofilter on columns D and E
Select column E filter and UNTICK Blanks so Blanks aren't listed.

This now gives a list of rows where only column D and E are filled.

Column D is either the start of a new range or the start of a contiguous range that began above that row.
Column E is blank if the row is contiguous otherwise it is the end value of a range.
But since you've eliminated the blanks using the filter

the result is a list (which can be copied elsewhere) of non-contiguous ranges of column A and B

ABC000000001 is a string but so is the 000000001 part of it.
The +0 turns 000000001 into 1 so it is regarded as a number so we can compare it with the next row down in column A to see if the next row is contiguous or not.

The ,0) is the end of the IFERROR() formula.
The last row of the data was generating a #VALUE error when evaluating IF(RIGHT(B6652,9)+1=RIGHT(A6653,9)+0,1,0)
So I just put the whole formula in an IFERROR() and returned 0 if the error occurs as the next row is not continguous as it's the end of the data.