So finden Sie Bereiche in Excel

Question

In Spalte A habe ich einige Zahlen, die aufgrund führender Nullen als Text formatiert sind:

+-------------+
|  A          |
+-------------+
| 01011111001 |
| 01011111002 |
| 01011111003 |
| 01011111012 |
| 01011111013 |
+------+------+

Ich verwende Spalte A als Eingabe und möchte zwei weitere Spalten, B und C, wie folgt füllen:

+-------------+--------------+
|  B          |  C           |
+-------------+--------------+
| 01011111001 | 010111111003 |
| 01011111012 | 010111111013 |
| 01011111115 |              |
| 01011111117 |              |
| 01011111019 | 0101111111020|
+------+------+--------------+

Wenn es eine Zahlenfolge gibt (z. B. einen Bereich), möchte ich die Startzahl in Spalte B und die Endzahl in Spalte C haben.

Wenn es sich jedoch um eine einzelne Zahl handelt, kann diese in Spalte B selbst verbleiben.

Ich habe 10000 Nummern, die Bereiche und einzelne Zahlen haben, die ich wie oben trennen möchte

Wie kann dies in Excel erreicht werden?

Answer 1

Verwenden erweiterter Beispieldaten

01011111002
01011111003
01011111004
01011111005
01011111006
01011111008
01011111009
01011111011
01011111012
01011111013
01011111014
01011111015
01011111016
01011111017
01011111018
01011111020
01011111023
01011111026
01011111027
01011111028
01011111031
01011111032
01011111033
01011111034

und vorausgesetzt, dass es sich um eine sortierte Liste handelt, können die folgenden Schritte hilfreich sein:

1) Definieren Sie einen Namen pool, der den Datenbereich + eine weitere Zelle umfasst, die leer ist.

2) Definieren Sie einen Namen nn, der die Größe des poolals

=ROWS(pool)

3) Definieren Sie einen Namen newrangeals Formel:

=MOD((-1+(N(OFFSET(pool,1,,nn-1)-OFFSET(pool,,,nn-1)<>1)*ROW(OFFSET(pool,,,nn-1)))),nn)+1

4) Wählen Sie den Bereich neben der poolSpalte „in“ aus Bund geben Sie eine Array-Formel ein (drücken Sie Ctrl+ Shift+ Enterstatt nur Enter):

=newrange 

Geben Sie 0unterhalb der letzten Zelle ein und wählen Sie:

5) Als nächstes kopieren Sie diese Auswahl, fügen sie als Werte ein und sortieren aufsteigend:

6) Geben Sie ein C1:

=IF(OFFSET($A$1,B1,)>0,OFFSET($A$1,B1,),"")

7) Geben Sie ein D1:

=IF(OFFSET($A$1,B2-1,)=C1,"",OFFSET($A$1,B2-1,))

8) Auswählen C1:D1und nach unten ziehen/kopieren, bis leere Zellen in der Spalte erscheinen C: