Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.05.2024 23:36:08
IFNA Funktion in WENNNV übertragen
16.05.2024 19:31:55
Zellenfarbe beim.Filtern ändern
16.05.2024 19:29:08
Mit INDIREKT() auf andere Tabellenblätter referenzieren
16.05.2024 19:06:22
Monatssumme / Zahlweise / Kalenderjahr
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1952to1956
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1952to1956
1952to1956
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Nur Teil eines Textes und nur in bestimmten Zeilen

Nur Teil eines Textes und nur in bestimmten Zeilen
03.11.2023 18:34:39
Christian
Hallo,

ich bitte euch um Hilfe bei der Formel in Spalte B.

Ihr seht, es gibt alle paar Zeilen (24, 34, 44, 53, 63 usw.) eine Aufzählung von insgesamt 300 Personen.

Die Formel soll künftig nur noch in diesen 300 Zeilen etwas ausgeben, nicht mehr in allen, in denen sie eine URL findet.

Und meine zweite Bitte, sie soll nur noch den Code der mit nm beginnt und weitergeht mit 8 oder 9 Ziffern ausgeben, nicht mehr die komplette URL.

Wie es aussehen soll, habe ich in Spalte C anhand der ersten 7 Personen gezeigt.

Wie mache ich das am Besten?

Danke
Christian

https://www.herber.de/bbs/user/164021.xlsm

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Nur Teil eines Textes und nur in bestimmten Zeilen
03.11.2023 21:28:03
Christian
Hallo Onur,

erstmal danke.
Aber es passt nicht so ganz. Was ist mit Person 7 oder Person 71? Also den Personen mit 10stelligem nm Code? Und ab Zeile 2980 hätte die Formel nichts mehr ausgeben sollen, da die 300. Person mit der 300. Ordnungszahl erreicht ist.

Schaust du bitte nochmal
Christian
AW: Nur Teil eines Textes und nur in bestimmten Zeilen
03.11.2023 21:44:18
onur
Warst DU das, der das schrieb, oder jemand Anderer?
"sie soll nur noch den Code der mit nm beginnt und weitergeht mit 8 oder 9 Ziffern ausgeben"
Du hast insofern Recht, MEIN Fehler war: Ich hätte anhand dessen Länge =10 oder 11 nehmen müssen.
Anzeige
AW: Nur Teil eines Textes und nur in bestimmten Zeilen
03.11.2023 21:57:03
Christian
Hallo Onur,

du hast meine Bitte offensichtlich falsch verstanden. Was du zitierst habe ich geschrieben, das stimmt. Obendrüber stand: Die Formel soll künftig nur noch in diesen 300 Zeilen (die mit den Ordnungszahlen von 1 bis 300) etwas ausgeben.

Der Satz sie soll nur noch den Code der mit nm beginnt und weitergeht mit 8 oder 9 Ziffern ausgeben war dann ebenfalls auf diese 300 Zeilen bezogen. und nur noch hatte bedeutet nicht mehr die komplette URL.

Gruß
Christian

AW: Nur Teil eines Textes und nur in bestimmten Zeilen
03.11.2023 22:02:45
onur
Wieso ziehst du dich jetzt an dieser blöden 300 hoch? Das habe ich noch gar nicht eingebaut.
Du schreibst: "NUR 8-9 ZIFFERN" und regst dich hier auf, dass 10-ZIFFERN nicht angezeigt werden!
Guckst du hier:
https://www.herber.de/bbs/user/164028.xlsm
Anzeige
AW: Nur Teil eines Textes und nur in bestimmten Zeilen
03.11.2023 22:19:56
Christian
Hallo Onur,

also erstmal ich rege mich nicht auf. Ich gehe davon aus, dass du ein Feedback erwartest, welches jede Kleinigkeit enthält, die nicht funktioniert hat. Unabhängig davon, ob ich es habe selber beheben können oder nicht. Sollte das unfreundlich bei dir angekommen sein, tut es mir leid.

Zu deiner Frage mit den 300, das sind die 300 die ich zum weiteren Bearbeiten benötige, die anderen sind für meine Zwecke überflüssig.

Danke auf jeden Fall für deine Hilfe
Christian
Frage nicht mehr offen owT
03.11.2023 22:20:20
Christian
.
AW: Frage nicht mehr offen owT
03.11.2023 22:23:07
onur
Ist das denn jetzt ok so?
AW: Frage nicht mehr offen owT
03.11.2023 22:42:50
Christian
Hallo Onur,

ich hab jetzt aus deiner Formel 

=WENNFEHLER(WENN(ISTZAHL(LINKS(A1;1)*1);LET(nm;SPALTENWAHL(TEXTTEILEN(GetURL($A1);"/");5);WENN(UND(LINKS(nm;2)="nm";ODER(LÄNGE(nm)=10;LÄNGE(nm)=9));nm;""));"");"")


gemacht, das scheint auf direktem Weg ohne Hilfsspalten das auszugeben, was ich mir vorgestellt hatte.

Danke
Christian
Anzeige
AW: Frage nicht mehr offen owT
03.11.2023 22:46:29
onur
Das war aber die ALTE Formel, ich hab dir doch in meiner vorletzten Antwort die neue Version gepostet (inklusive der 300). Nicht gesehen ?
AW: Frage nicht mehr offen owT
04.11.2023 07:12:13
Christian
die vorletzte Antwort enthält bei mir die Datei mit der Formel 

=WENN(ZÄHLENWENN(D$1:D70;"nm*")300;WENNFEHLER(LET(nm;SPALTENWAHL(TEXTTEILEN(GetURL($A71);"/");5);WENN(UND(LINKS(nm;2)="nm";ODER(LÄNGE(nm)=10;LÄNGE(nm)=9));nm;""));"");"")


danach kamen bei mir keine weiteren Formeln mehr an.

Und diese habe ich mit der Absicht geändert, dass es auch eine andere Anzahl als 300 sein kann. Dahin geändert dass sie überprüft ob der Text in Spalte A mit einer Ziffer beginnt.

Gruß
Christian
Anzeige
AW: Nur Teil eines Textes und nur in bestimmten Zeilen
03.11.2023 19:19:12
Yal
Hallo Christian,

Onur hat es schon wieder in einer Geschwindigkeit gelöst, bei der ich nicht mithalten kann... ;-)

Ich schlage eine Alternativ mit RegExp, da Used Defined Function bereit im Einsatz sind: 
Function RegExExtract(Zelle As Range, Muster As String)

Dim R

    Set R = CreateObject("VBScript.Regexp")

    R.Pattern = Muster

    RegExExtract = R.Execute(Zelle.Value)(0)

End Function


Dann in E24
=RegExExtract(B24;"nm\d{7,}")
spricht: extrahiere den Teilstring, bei dem der Muster "nm und Zahlen (mind. 7)" zutrifft.

Diese RegEx-Extraktor ist ziemlich universal einsetzbar. Spielen, testen unter https://regexr.com/

VG
Yal
Anzeige
AW: Nur Teil eines Textes und nur in bestimmten Zeilen
03.11.2023 21:51:17
Christian
Hallo Yal.

auch dir erstmal vielen Dank.

Könntest du bitte noch als Prüfung einbauen, dass der Text in Spalte A mit einer Ziffer beginnt, zusätzlich zu der Prüfung ob ein nm Code vorhanden ist? Damit wirklich nur die Codes aus der Auflistung ausgegeben werden und nicht wie in den Zeilen 2891 folgende auich Codes die nicht mit der Auflistung zu tun haben?

Außerdem warum funktioniert nur der indirekte Weg (Spalte D) aber nicht der direkte (Spalte E)

schaust du bitte nochmal danach?

https://www.herber.de/bbs/user/164027.xlsm

Danke
Christian
Anzeige
AW: Nur Teil eines Textes und nur in bestimmten Zeilen
03.11.2023 22:44:06
Yal
Hallo Christian,

die Prüfung, dass die Zelle in Spalte A mit einem Zahl anfängt, erreichst Du mit
=ISTZAHL(--RegExExtract(A24;"^\d"))
^ für Anfang und \d für einen numerischen Zeichen.


Der "direkter Weg"
=WENNFEHLER(RegExExtract(GETURL(A44);"nm\d{7,}");"")
schlägt fehl, weil "RegExExtrakt" in seine bisherigen Form eine Zelle als Quelle erwartet. GetUrl liefert aber einen String zurück.

Um das umzugehen, benutze diese neue Definition: 
Function RegExExtract(Zelle, Muster As String)

Dim R

    If TypeOf Zelle Is Range Then Zelle = Zelle.Value

    Set R = CreateObject("VBScript.Regexp")

    R.Pattern = Muster

    RegExExtract = R.Execute(Zelle)(0)

End Function

Wenn die Variable "Zelle" ein Range ist, wird seine Inhalt verwendet ("Zelle" ist dann nur noch einen String), ansonsten wird Zelle als String behandelt.

VG
Yal
Anzeige
AW: Nur Teil eines Textes und nur in bestimmten Zeilen
04.11.2023 07:26:14
Christian
Hallo Yal,

diese Formel gibt bei mir einfach nur FALSCH aus, Spalte D und E. Außerdem die Prüfung ob eine URL mit NM Code vorhanden ist, brauche ich ja (für den direkten Weg) trotzdem, damit nicht z.b. auch in Zeile 20 versucht wird, etwas auszugeben, da A20 ja auch mit einer Ziffer beginnt.

https://www.herber.de/bbs/user/164030.xlsm

Gruß
Christian
bekomme ich keine Antwort mehr? owT
06.11.2023 10:51:31
Christian
.
AW: bekomme ich keine Antwort mehr? owT
06.11.2023 11:07:06
Yal
Hallo Christian,

alles kommt zu rechtem Zeitpunkt für derjenige, der warten weisst. Ich bemühe mich, am Wochenende nicht vor dem Rechner zu sein.

Ja, die Formel
=ISTZAHL(--RegExExtract(A24;"^\d"))
liefert eine boolsche Antwort (ja/nein), ob der erste Zeichen ein Zahl ist oder nicht. Ziel ist es, darauf zu bauen und die Extraktion der "nm" nur zu machen, wenn es notwendig ist.

Im allgemein können wir nicht immer vollständige Lösung geben, sondern nur Bruchstück, die es zu kombinieren gilt. Es gibt verschiedene Lösung. Welche die beste ist, muss der Fragenden selber ausprobieren und entscheiden.
Es wäre natürlich auch möglich zu eine Frage eine genau passende Anwort zu liefern, aber aus Erfahrung kommt sofort dann "ja, aber wenn es dann so ist, funktioniert es nicht" (hatten wir gerade eben mit dem RegExExtrakt, der nur Zellenbezug haben wollte). Mit dem Baustein-Prinzip werden viel mehr Konstellationen abgedeckt. Aber der Fragende muss die Rolle des Bauherrs übernehmen.

VG
Yal
Anzeige
AW: bekomme ich keine Antwort mehr? owT
06.11.2023 11:34:46
Christian
Hallo Yal,

erstmal sorry. Das du das Wochenende frei haben willst, kann ich verstehen. Ich war wohl wirklich zu ungeduldig.
Zu dem anderen ich bin davon ausgegangen, dass ich irgendwo einen Fehler gemacht habe und ihn nicht finde. Deshalb habe ich gefragt, weshalb da plötzlich überall falsch steht.

Aber nichts desto trotz, ich bin deinem Vorschlag gefolgt und habe versucht einen eigenen Lösungsweg zu kreieren und habe es denke ich auch geschafft: 

=WENN(ISTZAHL(LINKS(A1;1)*1);WENNFEHLER(RegExExtract(GETURL(A1);"nm\d{7,}");"");"")


Ich sehe jetzt zumindest bei den aktuellsten Daten (neuer als die in der Bsp Datei) keine Zeile, in der diese Formel nicht funktioniert.

Danke
Christian
Anzeige
Vielen Dank für die Rückmeldung
06.11.2023 15:24:17
Yal
Hallo Christian,

sehr gut.
Die Version IstZahl(Links(A1;1)+1) ist sogar besser als die RegEx-Version, die, in dem Fall, overengineered ist.

Es fellt mir gerade ein, dass wenn der Wert in Spalte A nicht mit einem Zahl anfängt, dann keine "nm" in der Link ist. Daher wurde die Kurzversion auch funktionieren, da RegExExtract eine leere String liefert, falls Muster nichts vorhanden:
=WENNFEHLER(RegExExtract(GETURL(A1);"nm\d{7,}");"")

Alternative: zuerst die Zeile in Spalte A sortieren. Dann sind die Zelle mit Zahl vorn.

VG
Yal
AW: Vielen Dank für die Rückmeldung
06.11.2023 20:46:51
Christian
Hallo Yal, da irrst du dich leider. Schau bitte mal in meine allererste Bsp Datei, hier nochmal: https://www.herber.de/bbs/user/164021.xlsm

genauer in die Zellen A2891:A2915, da gibt es mehrere Texte die nicht mit einer Zahl beginnen, jedoch ein nm im Hyperlink haben.

Aber trotzdem danke für den Hinweis.
Christian
Ach ja
07.11.2023 07:08:56
Yal
Die Zeilen 2891 bis 2915...
Wie kann ich Sie übersehen haben!
AW: Ach ja
07.11.2023 10:52:02
Christian
Wegen diesen Zeilen gab es ja überhaupt diesen Teil der Bitte, das erste Zeichen in Spalte A zu prüfen.

Aber kein Problem, ich glaube auch nicht, dass ich an deiner Stelle mehrere 1000 Zeilen überprüft hätte.

Gruß
Christian

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige