PDA

Vollständige Version anzeigen : Bestimmte Zahlen aus Text extrahieren


Ghost232
24.08.2017, 10:04
Hallo zusammen :) :) !!!

ich habe folgendes Problem in Excel und hoffe, dass ihr mir da weiterhelfen könnt:

In der Spalte A stehen folgende Bezeichnungen:

AB.1.104
AB.1.04.1
AB.1.66.ABC2
AB.1.11
AB.1.101
AB.1.13.2

usw.


Wenn ich nun auf Sortieren gehe sieht es so aus:

AB.1.04.1
AB.1.101
AB.1.104
AB.1.11
AB.1.13.2
AB.1.66.ABC2

Problem: Ich hätte gerne die Daten von Excel nach den 2 bzw. 3-stelligen Zahlen sortiert und zwar so:

AB.1.04.1
AB.1.11
AB.1.13.2
AB.1.66.ABC2
AB.1.101
AB.1.104

Das hat bisher nicht geklappt:
-Die Daten von Standard auf Zahlenformat zu ändern und dann zu sortieren

Daher hoffe ich, dass ihr mir damit weiterhelfen könntet, ggf. mit einer Hilfsspalte und den SUCHEN(), FINDEN() und TEIL() - Funktionen. Ich komme damit leider nicht auf ein Ergebnis :-(

Liebe Grüße!

lupo1
24.08.2017, 10:30
Kopiere Deine Daten in Google Spreadsheet (Du wirst den Quatsch ja hoffentlich nur einmal machen!). Dort sortierst Du dann nach der Spalte mit der Formel

=ARRAYFORMULA(
WECHSELN(GLÄTTEN(TEXTVERKETTEN(" ";;TEXT(GLÄTTEN(TEIL(WECHSELN($A1;".";WIEDERHOLEN(" ";99));ZEILE(A$1:A$9)*99-98;99));"0000")));" ";".")
)
<pre><b>__|______A_____|_________B________|CODETab von lupo1<br>*1|AB.1.11 |AB.0001.0011 |<br>*2|AB.1.13.2 |AB.0001.0013.0002 |<br>*3|AB.1.66.ABC2|AB.0001.0066.ABC2 |<br>*4|AB.1.101 |AB.0001.0101 |<br>*5|AB.1000.104 |AB.1000.0104 |<br>*6|AB1.1.04.1 |AB1.0001.0004.0001|</b></pre>und kopierst die Daten zurück nach Excel. Nicht verunsichern lassen: Ich habe in Deinen Daten rumgespielt.

Hinweis: Excel 2016 kann die Formel auch schon. Du musst nur =ARRAYFORMULA(...) durch {=...} (http://xxcl.de/bitteBeachten) ersetzen. Dann entfällt der ganze Spagat.

Ghost232
24.08.2017, 12:17
Das funktioniert so leider nicht bei mir. Da kommt überall der Fehler #NAME?

Geht es nicht etwas unkomplizierter??

Danke trotzdem für deine Antwort!:)

Fennek11
24.08.2017, 12:24
Hallo,

lege eine Hilfsspalte an mit der Zahl nach dem zweiten Punkt, in VBA wäre das


Hilfszahl = split(cells(i,"A"), ".")(2)


Dann ist noch eine spezielle Behandlung der Buchstaben-Zahlen-Kombination notwendig.

mfg

lupo1
24.08.2017, 14:17
So antwortet man nicht! Wie soll ich wissen, "was nicht funktioniert"?

Du sollst Deine Daten in ein Google-Spreadsheet packen (ist kostenlos) oder Dir xl2016 kaufen.

Das bedeutet, dass Du mit Deinem Excel von 1781, geschneidert von Börries und Gates, mit meiner Formel NICHT weiter kommst.

Noch so ne Kleinmädchenantwort und ich beiße.

Dann der Versuch mit der zweitbesten Antwort:

=TEXT(GLÄTTEN(TEIL(WECHSELN($A2;".";WIEDERHOLEN(" ";99));SPALTE(A2)*99-98;99));"0000")

kopierst Du neben Deine Daten in so viele Spalten, wie Du brauchst (z.B. B:G). Jetzt sortierst Du nach EFG und dann nach BCD - oder in neueren Excels gleich nach BCDEFG.

drambeldier
24.08.2017, 15:05
Moin,

nutze Daten > Text ín Spalten und sortiere diese.

Frank Furter
25.08.2017, 12:45
...
Du sollst Deine Daten in ein Google-Spreadsheet packen (ist kostenlos) ...

Das bedeutet, dass Du mit Deinem Excel von 1781, geschneidert von Börries und Gates, mit meiner Formel NICHT weiter kommst.




Hallo lupo1, sorry, wenn ich dazwischen grätsche... ... ich kriege das (aber auch) nicht hin (vielleicht habe ich ja was übersehen..?)...:

https://s1.imagebanana.com/file/170825/yS4n8oC3.JPG (https://www.imagebanana.com/s/795/yS4n8oC3.html)

WS-53
25.08.2017, 13:06
Hallo Frank,

ich kann dein Ergebnis, nach einem Test direkt in Excel 2016, bestätigen. Ich habe dann aber noch etwas weiter getestet und die Formel recht schnell zum laufen gebracht.

Es muss einfach als eine normae Matrixformel erfasst werden, ohne dass dies mit ARRAFORMULA beginnt.

=WECHSELN(GLÄTTEN(TEXTVERKETTEN(" ";;TEXT(GLÄTTEN(TEIL(WECHSELN($A1;".";WIEDERHOLEN(" ";99));ZEILE(A$1:A$9)*99-98;99));"0000")));" ";".")

Dann kommt exakt das erwartete Ergebnis raus.

Frank Furter
25.08.2017, 13:11
Hallöchen, in OE kann man CSE nicht anwenden... deswegen ARRAFORMULA vorneweg... , ich wollte das so ausprobieren, aber bei mir klappt das halt so nicht... Dass das in XL2016 oder und höher klappt, glaube ich ja ungesehen... - kann ich ja auch nicht prüfen..!!!

drambeldier
25.08.2017, 14:51
Moin,

hier der simpelste Weg ohne jegliche Funktion:
<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:154px;" /><col style="width:118px;" /><col style="width:27px;" /><col style="width:18px;" /><col style="width:36px;" /><col style="width:45px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Courier New; ">Ausgangslage</td><td style="font-family:Courier New; ">AB.1.104</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.04.1</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.66.ABC2</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.11</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.101</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.13.2</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Courier New; ">Text in Spalten</td><td style="font-family:Courier New; ">AB.1.104</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">104</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.04.1</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">4</td><td style="font-family:Courier New; text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.66.ABC2</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">66</td><td style="font-family:Courier New; ">ABC2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.11</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">11</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.101</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">101</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.13.2</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">13</td><td style="font-family:Courier New; text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-family:Courier New; ">sortiert</td><td style="font-family:Courier New; ">AB.1.04.1</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">4</td><td style="font-family:Courier New; text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.11</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">11</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.13.2</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">13</td><td style="font-family:Courier New; text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.66.ABC2</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">66</td><td style="font-family:Courier New; ">ABC2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.101</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">101</td><td style="font-family:Courier New; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-family:Courier New; ">&nbsp;</td><td style="font-family:Courier New; ">AB.1.104</td><td style="font-family:Courier New; ">AB</td><td style="font-family:Courier New; text-align:right; ">1</td><td style="font-family:Courier New; text-align:right; ">104</td><td style="font-family:Courier New; ">&nbsp;</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4.8 </a>

lupo1
26.08.2017, 00:12
Hallo Frank Furter,

in meiner #2 steht (ungefähr):

- nix zu OE (denn da geht es nicht, wie sonst auch nicht)
- zu Google "verwende =ARRAYFORMULA(...)" (das ist eine Nicht-Excel-Formel, WS-53!), die in
- xl2016 durch {...} zu ersetzen ist.