Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1724to1728
1724to1728
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Längen Formel

Forumthread: Längen Formel

Längen Formel
30.11.2019 07:21:30
Christian
Hallo,
wie kann ich bei der Längen Formel bestimmen, dass die ersten beiden, sowie die letzten beiden Worte in A1 ausgelassen werden sollen?
Gruß
Christian
Anzeige

30
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
noch ein Beispiel
30.11.2019 07:22:37
Christian
in A1 steht
22 NWL Ahaus 6 Nordrhein-Westfalen
die Lösung soll 5 sein als Länge des Wortes Ahaus, weil die ersten beiden und die letzten beiden Worte nicht zählen sollen.
Gruß
Christian
AW: noch ein Beispiel
30.11.2019 07:59:22
Hajo_Zi
Hallo Christian,
ich habe es mal mit 2 Hiolfsspalten gemacht. Die Formel kann man bestimm noch zusammenfassen.

Tabelle2
 ABCD
122 NWL Ahaus 6 Nordrhein-Westfalen8135
2231 NWLk Ahause 6 Nordrhein-Westfalen10166

verwendete Formeln
Zelle Formel Bereich N/A
B1:B2=SUCHEN(" ";A1;SUCHEN(" ";A1)+1)+1  
C1:C2=SUCHEN(" ";A1;SUCHEN(" ";A1;SUCHEN(" ";A1;SUCHEN(" ";A1)+1)+1)+1)-2  
D1: D2=LÄNGE(TEIL(A1;B1;C1-B1))  



Anzeige
Unübersichtlich ...
30.11.2019 08:26:20
Matthias
Deine Tabellendarstellung ist nicht übersichtlich!
Es ist nicht erkennbar das nach Ahaus 6 ein Leerzeichen folgt.
AW: noch ein Beispiel
30.11.2019 13:38:44
Christian
Hallo Hajo,
irgendwas kann da leider nicht stimmen.
In der Zeile in der
3030 NWL Steinhagen (Westf) Bielefelder Straße 6 Nordrhein-Westfalen
steht gibt deine Formel in Spalte C 16 aus.
Wenn man die ersten beiden und die letzten beiden Worte streicht, bleibt
Steinhagen (Westf) Bielefelder Straße
übrig das sind eindeutig mehr als 16 Zeichen.
Gruß
Christian
Anzeige
Spalte D nicht C sorry owt
30.11.2019 13:39:56
Christian
.
Bsp Datei
30.11.2019 13:44:56
Christian
https://www.herber.de/bbs/user/133501.xlsx
Die Tabelle ist so aufgeteilt
Bahnhofsnummer (1 Zahl) Verkehrsverbund (1 Wort) dann der Bahnhofsname (1 oder mehrere Wörter) Bahnhofskategorie (1 Zahl) Bundesland (1 Wort)
Ich möchte gerne eine Ausgabe der Länge des Bahnhofsnamens.
Danke
Christian
Anzeige
AW: Bsp Datei
30.11.2019 17:17:51
Günther
Moin Christian,
ich habe einmal deine erste Definitionsbeschreibung für eine Lösung verwendet. Mit Ausnahme beispielsweise der Zeile 410 könnte das auch stimmen. Wenn ich deine hierüber beschriebene Definition zugrunde lege, ist das zwar auch mit deiner Version (2016) machbar, mir jedoch im Rahmen der Forenhilfe zu aufwändig.
https://www.herber.de/bbs/user/133505.xlsx
Gruß
Günther
Anzeige
AW: Bsp Datei
30.11.2019 18:04:58
Christian
Hallo Günther, super das ist ja das was ich wollte. Nur was ist mit Zeile 410 los. da blicke ich jetzt gar nicht mehr durch.
Gruß
Christian
AW: Bsp Datei
30.11.2019 20:18:11
Günther
Na ja Christian
Zeile 410 sieht ja so aus: 

8326 VRR Emmerich-Elten 6 Nordrhein-Westfalen IBN 09.06.2019

Ich habe die ersten beiden Begriffe (jeweils durch ein Leerzeichen getrennt) entfernt und dann die letzten beiden Begriffe. Das Ergebnis siehst du ja in der Tabelle. Denn die letzen beiden Begriffe sind ja IBN 09.06.2019.
Mit Excel 2019/365 wäre es problemlos und auch mit wenigen Mausklicks möglich, die Zahlen als Trenner zu definieren. Und es geht (wie schon angedeutet) auch mit Excel 2016. Entweder auch über den Weg Power Query oder natürlich auch per VBA.
Gruß
Günther
Anzeige
AW: Bsp Datei
01.12.2019 08:08:06
Christian
Hallo Günther,
erst einmal ich nutze Excel 2019, man kann diese Version nur leider bei der Versionsangabe in diesem Forum nicht auswählen.
Danke auch für den Hinweis in Zeile 410, war mir vorher noch gar nicht aufgefallen. Hab jetzt den zusäzlichen Text in deiner Tabelle1 gelöscht und Tabelle2 aktualisiert.
Danke auf jeden Fall und Gruß
Christian
Anzeige
AW: Bsp Datei
01.12.2019 18:37:47
Günther
Moin,
mit 2019 ist das (natürlich) deutlich einfacher bzw. stringenter ... ;-)
Und erfreulicherweise ohne eine einzige Formel, nur ein paar Mausklicks.
Meine Bedingung jetzt: Alles zwischen der ersten und dem 2. Zahl ist der Bahnhofsname.
(Ich kann es mir nicht verkneifen: "Ich verstehe nur "Bahnhof" :-)) )
=> https://www.herber.de/bbs/user/133519.xlsx
Gruß
Günther
Anzeige
AW: Excel & Access Add-Ins
01.12.2019 06:35:31
Case
Hallo Christian, :-)
... hier zwei Möglichkeiten: ;-)
Deine Datei zurück...
Einmal als UDF...
Hier wird es in Spalte C ausgegeben. ;-)
Und einmal per VBA. Klick auf den Button füllt Spalte B aus. ;-)
Servus
Case
Anzeige
Uuppss... Glatt vergessen, den Titel anzupassen ;)
01.12.2019 06:39:27
Case
:-)
AW: Excel & Access Add-Ins
01.12.2019 08:12:35
Christian
Hallo Case,
leider passt das nicht so ganz, ich zweifele dass in Zeile 668
1939 NVR Friedrich Wilhelmshütte 6 Nordrhein-Westfalen
der Bahnhof nur aus 9 Zeichen besteht.
Aber schonmal danke für die Mühe
Christian
Zweite Trennung - Definition...
01.12.2019 08:32:18
Case
Hallo Christian, :-)
... an der Zahl? Soll hier...
4425 NWL Neunkirchen (Kr Siegen) 6 Nordrhein-Westfalen
... also "(Kr Siegen)" mit dazu kommen? Bzw. auch an anderen Stellen, der Zusatz immer dazu?
Servus
Case
Anzeige
AW: Zweite Trennung - Definition...
01.12.2019 08:48:22
Christian
ja das gehört zum Bahnhofsnamen dazu
Sollte so eigentlich...
01.12.2019 10:58:30
Case
Hallo Christian, :-)
... passen:
Angepasste Datei...
Durch Array auch recht flott. ;-)
Kann man natürlich auch noch als UDF schreiben, sofern man es als Formel eingeben möchte. ;-)
Servus
Case
Anzeige
AW: Sollte so eigentlich...
01.12.2019 11:41:11
Christian
Hallo Case,
ja das scheint zu passen. Danke
Un jetzt ob Button oder UDF, das ist mir gleich.
Hatte nur Anfangs von einer Formel gesprochen, weil ich dachte das wär mit den normalen Excel Formeln lösbar und damit einfacher als ein Makro.
Gruß
Christian
Ich denke, du solltest das...
01.12.2019 15:47:22
Case
Hallo Christian, :-)
... nicht falsch verstehen - du musst das nicht per Makro machen, das ist nur eine Möglichkeit. ;-)
Der Eine macht es gerne mit Formeln, der Nächste mit Power Query und der Dritte schließlich mit VBA. Jeder so wie er kann bzw. wo die Vorlieben liegen. Ich z. B. erledige alles - wenn möglich - mit VBA. ;-)
Wenn du weder Formeln noch Makros beherrschst, dann nimm Power Query. Das ist für Datenmanipulation wie geschaffen. Es gib da auch massig Anleitungen im Netz.
Servus
Case
Anzeige
Aber doch mal noch eine allgemeine Frage
01.12.2019 12:11:57
Christian
man kann doch mit
=SUCHEN(" ";A1)
die Position des ersten Leerzeichens herausfinden.
Dann kann man doch bestimmt auch irgendwie die Position des zweiten un des vorletzten Leerzeichens rausfinden.
Und damit wäre doch die Differenz der beiden Positionen meine gesuchte Länge. Warum dann der Umstand eines Makros?
Gruß
Christian
Anzeige
AW: Aber doch mal noch eine allgemeine Frage
01.12.2019 14:40:08
Hajo_Zi
Hallo Christian,
=SUCHEN(" ";A1,SUCHEN(" ";A1)+1)

AW: Schreibfehler: "," durch ein ";" ersetzen owT
01.12.2019 15:45:59
neopa
Gruß Werner
.. , - ...
AW: Position eines x-ten Leerzeichens finden ...
01.12.2019 15:43:31
neopa
Hallo Christian,
... kann in Deiner Excelversion ganz allgemein auch mit AGGREGAT() gefunden werden. Für einen Text z.B. in A9
Von vorn "gezählt" so: =AGGREGAT(15;6;ZEILE(A$1:A$99)/(TEIL(A9;ZEILE(A$1:A$99);1)=" ");x)
(worin das x durch eine Zahl ersetzt werden muss, die der Pos.-Nr. entspricht)
Von hinten "gezählt" muss in der Formel lediglich das erste Argument der Funktion, die 15, durch eine 14 ersetzt werden.
Die Formel ist zwar länger als der Vorschlag von Hajo, dafür aber auch gleich für ein z.B. 5. Auftreten (von vorn oder hinten) einsetzbar
Gruß Werner
.. , - ...
Anzeige
Textlänge zwischen bestimmten Leerzeichen
01.12.2019 16:39:07
Josef
Hallo
Werner hat mich auf eine Idee gebracht.
Warum bei der Funktion AGGREGAT nicht die 14 und 15 als Argument einsetzten?
Damit wird Aufgabe von Christian relativ elegant gelöst.
Formel für A2:
=SUMME(AGGREGAT({14;15};6;ZEILE(A$1:A$99)/(TEIL(A2;ZEILE(A$1:A$99);1)=" ");2)*{1;-1})-1
Gruss Sepp
Anzeige
AW: sehr schön, wobei ...
01.12.2019 17:31:10
neopa
Hallo Sepp,
... hierbei die Gefahr bestehen könnte, dass man verkennt, ob wirklich 5 "Wörter" vorhanden sind.
Deshalb mein Vorschlag zur Ergänzung Deiner Formel:
=WENN(LÄNGE(GLÄTTEN(A2))-LÄNGE(WECHSELN(GLÄTTEN(A2);" ";""))4;"keine 5 Wörter";
SUMME(AGGREGAT({14;15};6;ZEILE(A$1:A$99)/(TEIL(GLÄTTEN(A2);ZEILE(A$1:A$99);1)=" ");2)*{1;-1})-1)
oT.: wünsche noch einen schönen 1. Adventsabend
Gruß Werner
.. , - ...
Anzeige
AW:es dürfen auch mehr als 5 Wörter sein
01.12.2019 18:15:53
Josef
Hallo Werner
Cristian hat in seinem Beispiel Texte mit bis zu 7 Wörtern.
Wenn keine 5 Wörter in einer Zelle sind, dann müsste das richtige Ergebnis null sein.
Dazu folgende kleine Erweiterung der Formel:
=MAX(0;SUMME(AGGREGAT({14;15};6;ZEILE(A$1:A$99)/(TEIL(A2;ZEILE(A$1:A$99);1)=" ");2)*{1;-1})-1)
Gruss Sepp
Anzeige
AW: auch dann mE GLÄTTEN() notwendig owT
01.12.2019 19:54:03
neopa
Gruß Werner
.. , - ...
Hier noch mit "aufgehobener Symmetrie":
01.12.2019 20:02:25
lupo1
=LÄNGE(TEXTVERKETTEN(" ";;GLÄTTEN(TEIL(WECHSELN(A2;" ";WIEDERHOLEN(" ";99));ZEILE(INDIREKT("3:6")) *99-98;99))))
ergibt - wie bei Max - 26 für den Dettmar-Cramer-String:
A2: Aus einem verzagten Arsch kommt kein fröhlicher Furz
=LÄNGE(TEXTVERKETTEN(" ";;GLÄTTEN(TEIL(WECHSELN(A2;" ";WIEDERHOLEN(" ";99));ZEILE(INDIREKT(A1&":"&B1) )*99-98;99))))
positioniert die Leerzeichen von vorn nun beliebig.
Mit B1: =LÄNGE()-LÄNGE(WECHSELN())-C1 geht das natürlich auch separat von hinten.
Anzeige
jetzt blicke ich gar nicht mehr durch owT
01.12.2019 20:28:28
Christian
.
Nimm Josef B's letzte Formel. Die ist schön!
01.12.2019 20:33:58
lupo1
... wobei neopa dafür die Vorlage geliefert hat.
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige