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

Suchen in einem String z.B. ab @ nach Rückwärts

Forumthread: Suchen in einem String z.B. ab @ nach Rückwärts

Suchen in einem String z.B. ab @ nach Rückwärts
16.11.2017 20:09:44
Siegfried
Ich habe z.B. den String
Gästewertung: 4.3 (10); +43 6227 3228 0; Stellplätze: 135; 24.70 EUR (inkl. 2 Erw.); Öffnungszeiten: 01.05.-30.09.; info@seecamping-primus.at; Schwand 39; [Abersee]
und möchte die E-Mailadresse heraus filtern.
Suche daher nach @ und lasse von dieser Position mir den Wert des nächsten ; errechnen.
Nun benötige ich aber noch die Position des ; vor dem @ um einen Teil des String auszulesen.
Gibt es eine Möglichkeit die Funktion SUCHEN auch in die andere Richtung auszulösen?
Oder wie könnte man das lösen,
Ich sollte eigentlich den gesamten String in Einzelteile nach den ; getrennt zerlegen?
DANKE für eine Lösungsrückmeldung, Siegfried
Anzeige

43
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit FINDEN() und weiteren TEXTfunktionen ...
16.11.2017 20:21:28
...
Hallo Siegfried,
... wenn Dein String in A1 steht, dann folgende Formel:
=GLÄTTEN(TEIL(LINKS(A1;FINDEN(";";A1&";";FINDEN("@";A1))-1);VERWEIS(9;1/(TEIL(A1;ZEILE(A1:INDEX(A:A;FINDEN(";";A1&";";FINDEN("@";A1))-1));1)=";");ZEILE(A:A))+1;99))
Gruß Werner
.. , - ...
anderer Ansatz
16.11.2017 21:49:30
WF
Hi,
String in A1
=GLÄTTEN(TEIL(WECHSELN(A1;";";WIEDERHOLEN(" ";199));(SUMMENPRODUKT((TEIL(LINKS(A1;FINDEN("@";A1)); ZEILE(X1:X999);1)=";")*1)+1)*199-198;199))
WF
Anzeige
AW: kommt mir bekannt vor owT
16.11.2017 21:55:28
...
Gruß Werner
.. , - ...
AW: kommt mir bekannt vor owT
16.11.2017 23:55:06
Siegfried
Dies funktioniert leider auch nur teilweise habe es in der Spalte M weiter kopiert, Daten stehen in dr Spalte C, in Zeile 39 kam dann als Ergebnis "[GB-HR2 0SF] Poston Mill Park" obwohl in C die Mailadresse Park; enquiries@poston-mill.co.uk; Pete vorhanden ist.
Die Formel in Zeile 39 sieht so aus -
=WENN(ISTFEHLER(FINDEN("@";C39;1));"";GLÄTTEN(TEIL(WECHSELN(C39;";";WIEDERHOLEN(" ";199)); (SUMMENPRODUKT((TEIL(LINKS(C39;FINDEN("@";C39)); ZEILE(O38:O1033);1)=";")*1)+1)*199-198;199)) )
Gruß Siegfried
Anzeige
Dein Wunsch nach Aufteilung des Datensatzes
17.11.2017 07:02:49
lupo1
Da es sich vermutlich um strukturierte Daten wie ein CSV handelt (auch wenn dort der Feldname nicht in die Datenfelder B2[:I2]: hineingehört, sondern in eine Feldnamenzeile darüber), könnte auch folgendes für die bloßen Inhalte J2[:Q2]: des Datenfeldes gehen:
A2: Dein Datensatz
B2[:I2]: =GLÄTTEN(TEIL(WECHSELN($A2;";";WIEDERHOLEN(" ";299));SPALTE(A1)*299-298;299))
als Hilfszellen
J2[:Q2]: =GLÄTTEN(TEIL(WECHSELN(B2;":";WIEDERHOLEN(" ";99));(LÄNGE(B2)+1-LÄNGE(WECHSELN(B2;":";)))*99-98;99))
Die Aufteilung selbst wird hier beschrieben.
Anzeige
Die Hilfszellen können noch weg ...
17.11.2017 07:17:17
lupo1
... klappt aber nur, wenn die Daten in ihrer Unvollkommenheit wenigstens verlässlich sind:
B2[:I2]:
=GLÄTTEN(TEIL(WECHSELN(
WECHSELN(WECHSELN(WECHSELN($A2;"Gästewertung:";);"Stellplätze:";);"Öffnungszeiten:";)
;";";WIEDERHOLEN(" ";299));SPALTE(A1)*299-298;299))
(mit dem Vor-Beitrag bin ich aus Versehen unter WF's Beitrag geraten)
Anzeige
AW: mit FINDEN() und weiteren TEXTfunktionen ...
16.11.2017 23:59:51
Siegfried
Dies funktionierte überhaupt leider nicht!
Variante von owt dann teilweise
WF war etwas schlampig und hat den Zähler ...
17.11.2017 01:27:57
Luc:-?
…nicht fixiert, Siegfried,
denn dann hätte seine (längere) Fml besser fktioniert als die von „owT“ (⇒ohne weiteren Text → Netzjargon, auf Wikipedia nachlesbar!), womit du wohl Werner (neopa) meinst. Walter (WF) hat hier (zT) eine Idee von Lupo1 aufgegriffen. ZEILE(X1:X999) ist hierbei ein Zähler, der immer von 1…999 läuft und deshalb nicht relativ geschrieben wdn darf, damit er nicht an die jeweilige FmlStandortZelle angepasst wird. Wenn die Texte zeilenweise untereinander stehen, reicht folgd Schreibung (in Bezug auf A1), die dann hinunterkopiert wdn kann:
=GLÄTTEN(TEIL(WECHSELN(A1;";";WIEDERHOLEN(" ";199));(SUMMENPRODUKT((TEIL(LINKS(A1;FINDEN("@";A1)); ZEILE(X$1:X$999);1)=";")*1)+1)*199-198;199))
Die beiden Fmln liefern für …
Gästewertung: 4.3 (10); +43 6227 3228 0; Stellplätze: 135; 24.70 EUR (inkl. 2 Erw.); Öffnungszeiten: 01.05.-30.09.; info@seecamping-primus.at; Schwand 39; [Abersee]
[GB-HR2 0SF] Poston Mill Park; enquiries@poston-mill.co.uk; Pete
…ff Ergebnisse:
WF: info@seecamping-primus.at — neopa: 9.; info@seecamping-primus.at
beide: enquiries@poston-mill.co.uk
Ansonsten gäbe es neben Ransis spezieller noch allgemeinere UDFs, die in einer (kürzeren) ZellFml eingesetzt wdn könnten (mal 3 unterschiedliche, zT auf Splitting basierende Varianten):
=PickOn(A1;VERGLEICH("*@*";VSplit(A1;"; "););"; ")
=Splint(A1; "; ";VERGLEICH("*@*";VSplit(A1;"; ");))
=INDEX(VSplit(A1; "; ");VERGLEICH("*@*";VSplit(A1;"; ");))
Die unterstrichenen UDF-Namen sind verlinkt und maussensitiv (Info-Text).
Feedback nicht unerwünscht! Morrn, Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …
Anzeige
AW: wieso das? ...
17.11.2017 09:39:18
...
Hallo Siegfried,
... warum und wann soll meine Formel nicht und die von WF nur teilweise funktionieren?
Dies kann natürlich dann der Fall sein, wenn Dein Datenstring einen anderen Inhalt hat, als von Dir bisher angegeben (z.B. weitere Mailadresse oder ein weiteres "@").
Denn siehe:
 ABC
1Gästewertung: 4.3 (10); +43 6227 3228 0; Stellplätze: 135; 24.70 EUR (inkl. 2 Erw.); Öffnungszeiten: 01.05.-30.09.; info@seecamping-primus.at; Schwand 39; [Abersee]info@seecamping-primus.atinfo@seecamping-primus.at

Formeln der Tabelle
ZelleFormel
B1=GLÄTTEN(TEIL(LINKS(A1;FINDEN(";";A1&";";FINDEN("@";A1))-1); VERWEIS(9;1/(TEIL(A1;ZEILE(A1:INDEX(A:A;FINDEN(";";A1&";";FINDEN("@";A1))-1)); 1)=";"); ZEILE(A:A))+1;99))
C1=GLÄTTEN(TEIL(WECHSELN(A1;";";WIEDERHOLEN(" ";199)); (SUMMENPRODUKT((TEIL(LINKS(A1;FINDEN("@";A1)); ZEILE(X1:X999); 1)=";")*1)+1)*199-198;199))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
Ich hatte das doch erklärt, Werner! Gruß owT
17.11.2017 18:16:25
Luc:-?
:-?
AW: und ich konnte das nicht verstehen, ...
18.11.2017 10:04:32
...
Hallo Luc,
... denn auch in meiner Formel war mein Zähler nur relativ gesetzt. Also war auch ich "schlampig". Ich wollte nur zum Ausdruck bringen, da sowohl WF#s als auch meine Formel prinzipiell das zunächst angefragte richtig ermittelt. Ich hab dann auf eine "Verabsolutierung" des Zählers verzichtet, weil Siegfried offensichtlich was ganz anderes anstrebt.
Gruß Werner
.. , - ...
Anzeige
Nun, siehe sein Xl-Level, was mangelnde ...
19.11.2017 03:57:09
Luc:-?
…Erfahrung mit Fmln im Allgemeinen und Zählern in denselben im Besonderen erkennen lässt, Werner;
und natürlich will er am liebsten wohl alles splitten (war mir klar und geht mit meinen UDFs ja auch), aber ihr hattet euch natürlich nur auf sein HptAnliegen konzentriert…
Morrn & schöSo, Luc :-?
Anzeige
oder etwas VBA...
16.11.2017 20:40:16
ransi
Hallo,
Schau mal ob du dich hiermit anfreunden kannst:
Tabelle1

 ABCD
1Gästewertung: 4.3 (10); +43 6227 3228 0; Stellplätze: 135; 24.70 EUR (inkl. 2 Erw.); Öffnungszeiten: 01.05.-30.09.; info@seecamping-primus.at; Schwand 39; ert@iiuhdsfdik.bf[Abersee]info@seecamping-primus.atert@iiuhdsfdik.bf#BEZUG!

Formeln der Tabelle
ZelleFormel
B1=INDEX(Email_Filter($A1); SPALTE(A1))
C1=INDEX(Email_Filter($A1); SPALTE(B1))
D1=INDEX(Email_Filter($A1); SPALTE(C1))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Dazu muss dieser kleine Code in ein Modul:
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit


Public Function Email_Filter(strB As String) As Variant
    Dim varTmp() As Variant
    Dim Regex As Object
    Dim M
    Dim Treffer
    Dim lngIndex As Long
    Set Regex = CreateObject("Vbscript.regexp")
    With Regex
        .Pattern = "\b(\w[-.\w]*@\w[-.\w]*\.[a-zA-Z]{2,6})\b"
        .IgnoreCase = True
        .Global = True
        Set Treffer = .Execute(strB)
        Redim varTmp(Treffer.Count - 1)
        For Each M In Treffer
            varTmp(lngIndex) = M.Value
            lngIndex = lngIndex + 1
        Next
    End With
    Email_Filter = varTmp
End Function


ransi
Anzeige
AW: oder etwas VBA...
16.11.2017 23:47:43
Siegfried
Funktioniert leider nicht, bekomme einen #BEZUG! Fehler.
Frage mich wo wird hier z.B. das zu suchende @ übergeben?
Möchte in der Spalte M die Auswertung aus der Spalte C stehen haben / M2 =INDEX(Email_Filter($C2); SPALTE(C2))
Ransis UDF ist für mehrere Mail-Adressen ...
17.11.2017 02:02:27
Luc:-?
…pro Text ausgelegt, Siegfried;
hast du nur jeweils eine, reicht auch diese Fml für einen Text in A1:
=INDEX(Email_Filter(A1);1)
Ja, eigentlich ist dann sogar diese ausreichend: =Email_Filter($A1)
INDEX wird nur benötigt, wenn mehrere im Text vorhanden sind, was Ransis Bsp auch zeigt! Den #BEZUG!-Fehler liefert allein INDEX, weil du SPALTE(C2) angegeben hast und das ergibt 3. Du hast aber doch wohl keine 3 Mail-Adressen in einem Text, so dass auch keine 3. vorhanden ist! RTFM! :-]
Das @ muss deshalb nicht angegeben wdn, weil das jede eMail-Adresse enthält und die UDF, wie schon ihr Name sagt, genau nach solchen filtert (steht im Suchmuster, Pattern, der UDF, die mit regulären Ausdrücken arbeitet). Und natürlich fktioniert die zugehörige Fml genausogut wie fast alle anderen Fmln! Viell liegt dein Misserfolg ja in deinem (angegebenen) Level begründet und du hast wirklich kaum Xl-Kenntnisse, geschweige denn von VBA. Bei Ersterem also meine obigen Hinweise beachten und bei Letzterem die ebenfalls oben verlinkte BspDatei herunterladen, dann siehst du auch, wo die UDF-Pgmm hineinkopiert wdn müssen. Der Xl-Kopf sollte dann aber auch den Reiter Entwicklertools aufweisen, den du dafür anklicken musst (anschld Visual Basic, alternativ auch gleich TastenKombi [alt][F11]).
Gruß, Luc :-?
Anzeige
Wenn Aufbau immer gleich
17.11.2017 09:43:21
RPP63
Moin!
Wenn die E-Mail-Addi immer an sechster Stelle ist:
Sub RPP() Const myString$ = "Gästewertung: 4.3 (10); +43 6227 3228 0;" & _ "Stellplätze: 135; 24.70 EUR (inkl. 2 Erw.);" & _ "Öffnungszeiten: 01.05.-30.09.; info@seecamping-primus.at;" & _ "Schwand 39; [Abersee]" Debug.Print Split(myString, ";")(5) End Sub Gruß Ralf
Anzeige
AW: Wenn Aufbau immer gleich
17.11.2017 12:43:26
Siegfried
Hi Ralf,
nun das ist leider nicht der Fall, den wenn kein Zelleninhalt wird auch dafür kein Trennzeichen ; gesetzt wodurch die Postion von der Mailadresse unterschiedlich ist.
Hätte nun versucht mit der funktionierenden Funktion von RANSI auch die anderen werte wie
Gästewertung, TelNr, Stellplätze, Preis (EUR), Öffnungszeiten, Straße und Ort heraus zu lesen, obwohl ich im .Pattern das @ gegen die ersten drei Buchstaben des Suchbegriffen ersetzte, macht er leider auch nicht. Kenn mich da etwas zu wenig aus!!!
Anzeige
AW: wenn es nicht nur um die Mailadresse geht ...
17.11.2017 12:53:15
...
Hallo Siegfried,
... dann nutze doch gleich die Funktion Text in Spalten (zu finden im Menü Daten) und trenne Deine Textwert über das Trennzeichen ";" dann bist Du weder auf Makros noch Formeln angewiesen.
Gruß Werner
.. , - ...
Das ist nach dem Vorbeitrag ja nun ...
17.11.2017 13:03:56
lupo1
... nicht mehr ganz so einfach, da sich aufgrund fehlender Semikola Feldverschiebungen ergeben.
Bleibt tatsächlich nur noch eine inhaltliche Analyse jeden einzelnen Feldes.
Anzeige
AW: wenn es nicht nur um die Mailadresse geht ...
17.11.2017 15:19:15
Siegfried
Hi Werner,
durch deinen Hinweis Text in Spalten und dort zu finden (zu finden im Menü Daten) habe ich jetzt für die Zukunft eine echte Arbeitserleichterung wenn die ; nach jeder Spalte gesetzt werden, mensch was habe ich mich oft geplagt dies über Suchen usw. zu erstellen.
In diesem FAll leider nicht die gewünschte Lösung da ja jetzt in einer Spalte nur dann die gleichen Werte stehen, wenn die Anzahl der ; die gleiche war.
Die Farge ist wie müsste die Function für die E-Mailsuche abgeändert werden, dass auch nach den anderen Begriffen gesucht werden kann, ist mir leider noch nicht gelungen!
Gruß Siegfried
Anzeige
AW: das wird dann so nur schwer möglich sein, ...
17.11.2017 15:32:47
...
Hallo Siegfried,
... denn ohne eindeutige "Beziehungwn", wie es bei der E-Mail Adresse das "@" -Zeichen ist, sind die anderen Datenwerte schon wesentlich probelmatischer zu erkennen. Die Telefonnummer vielleicht, wenn überall diese mit "+" beginnen sollte. Oder wenn überall stets die gleiche Kennung wie z.B. "Öffnungszeiten" etc. vorangestellt oder eingebunden sind.
Gruß Werner
.. , - ...
Anzeige
AW: das wird dann so nur schwer möglich sein, ...
18.11.2017 22:11:40
Siegfried
Hallo Werner,
nun die Auswertung mit @ funktioniert bestens.
Hätte nun versucht das gleiche mit dem + der TelNr durchzuführen in dem ich im .Pattern = "\b(\w[-.\w]*@\w[-.\w]*\.[a-zA-Z]{2,6})\b" das *@ durch *+ ersetzte, funktionierte mir leider nicht.
Was mache ich da falsch oder verstehe ich nicht?
Wie müsste es geschrieben werden das es die TelNr, oder die anderen fixen Werte wie "Gästewertung", "Stellplätze", "Öffnungszeiten" oder [Ortsname] herausfindet.
Die [PLZ] und "Campingname" in der ersten und zweiten Spalte auszulesen ist ja kein Problem, was auch noch etwas schwieriger ist, ist der "Straßenname" welcher eine Position vor dem [Ortsnamen] liegt.
Was auch möglich wäre ist die Anregung von neopa C und lupo1 vom 17.11.17 mit Text in Spalten, hier habe ich aber ein großes Problem mit dem Hinweis von lupo1, (Bleibt tatsächlich nur noch eine inhaltliche Analyse jeden einzelnen Feldes.).
Habe es über ständige Filtersetzung in jeder Spalte nach langem Zeitaufwand fast geschafft, was gäbe es da für eine Makro-Lösungsmöglichkeit, habe dazu zu wenig Erfahrung?
Gruß Siegfried
Anzeige
AW: das wird dann so nur schwer möglich sein, ...
19.11.2017 00:50:49
Siegfried
Hier bitte eine Musterdatei meines Problems, DANKE für eine brauchbare Lösung!
Gruß Siegfried
AW: meinerseits hierzu angemerkt ...
19.11.2017 10:14:19
...
Hallo Siegfried,
... ich beschäftige mich ausschließlich mit Excel-Standardlösungen und Formeln ohne VBA. U.a. aus diesem Grund downlade ich mir keine xlsm Datei aus dem Internet. Wenn Du weiterhin auch Formellösungen suchen solltest, stelle Deine Datei als xlsx-Datei ein.
Gruß Werner
.. , - ...
Anzeige
AW: gleiche Formel für Mail-Adr. oder Tel.Nr. ...
19.11.2017 10:57:09
...
Hallo Siegfried,
... mit einen kleinen aber entscheidenden Unterschied
Mailadresse, in L2:
=WENNFEHLER(GLÄTTEN(TEIL(LINKS(C2;FINDEN(";";C2&";";FINDEN("@";C2))-1);VERWEIS(9;1/(TEIL(C2;ZEILE(A$1:INDEX(A:A;FINDEN(";";C2&";";FINDEN("@";C2))-1));1)=";");ZEILE(A$1:A$999))+1;99));"")
und Telefonnummer, in G2:
=WENNFEHLER(GLÄTTEN(TEIL(LINKS(C2;FINDEN(";";C2&";";FINDEN("+";C2))-1);VERWEIS(9;1/(TEIL(C2; ZEILE(A$1:INDEX(A:A;FINDEN(";";C2&";";FINDEN("+";C2))-1));1)=";");ZEILE(A$1:A$999))+1;99));"")
Wie Du siehst, der Unterschied beider Formel besteht lediglich in dem zu suchenden Zeichen (hier das "@" und dem "+")
Die Formeln setzen jedoch voraus, dass im auszuwertenden String das jeweilige Zeichen auch nur einmal vorhanden ist.
Gruß Werner
.. , - ...
Anzeige
AW: gleiche Formel für Mail-Adr. oder Tel.Nr. ...
19.11.2017 11:28:07
Siegfried
HAllo Werner,
alsooooo du bist der Größte, diese Formel, welche auch noch nach anpassen des entsprechende Suchkriterium in allen Spalten funktioniert, hätte ich nie hin gebracht.
DANKE da habe ich mal wieder was dazugelernt, muss es aber erst noch echt studieren bis ich da alles davon kapiere.
DANKE Gruß Siegfried
Anzeige
AW: gleiche Formel für Mail-Adr. oder Tel.Nr. ...
19.11.2017 12:21:27
Siegfried
Werner,
ich habe mich wohl etwas zu früh gefreut!
Für die Suche der Straßennamen habe ich als Suchkriterium ; [ vom Ort eingegeben.
Dort wo es einen Straßennamen gibt funktioniert es auch, wenn der aber fehlt bringt er natürlich den vorhergehenden Wert, werde ich wohl von Hand aus lösen müssen
Anzeige
AW: mit WECHSELN kommst Du den Str.Namen näher ...
19.11.2017 13:33:53
...
Hallo Siegfried,
... zuvor natürlich zunächst die Daten in H:K ermitteln. Dafür hab ich ohne die Ergebnisse jetzt näher zu prüfen, folgende Suchkriterien in meine Basisformel eingesetzt:
für Wertung "Gäst", für STP "Stell", für Preis "EUR" und für Offen "Öff".
Ich bin weiter davon ausgegangen, dass die Adressdaten vor dem Ortsnamen stehen. Dann folgende Formel für Straße:
=WENNFEHLER(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(TEIL(LINKS(C2;FINDEN(N2;C2)-3);
VERWEIS(9;1/(TEIL(LINKS(C2;FINDEN(N2;C2)-3);ZEILE(A$1:INDEX(A:A;LÄNGE(LINKS(C2;FINDEN(N2;C2)-3))));
1)=";");ZEILE(A$1:A$999))+2;999);L2;"");K2;"");J2;"");I2;"");H2;"");G2;"");"")
Nun sollte sich die evtl. noch notwendige "Handarbeit" auf ein Minimum reduzieren.
Gruß Werner
.. , - ...
Anzeige
AW: mit WECHSELN kommst Du den Str.Namen näher ...
22.11.2017 19:41:39
Siegfried
Hallo Werner,
meine Antwort hat wegen etwas Stress, bin momentan auf REHA leider gedauert.
Nun hat sehr gut bis auf wirklich Kleinigkeiten funktioniert, war echt eine spitze Lösung von dir.
Man hat ja leider nicht immer Daten mit solchen markanten Suchwerten wie "Gäst", für STP "Stell", für Preis "EUR" und für Offen "Öff" oder den Ortsnamen am Ende des String usw. wodurch diese Lösung erst möglich war.
Lange Rede kurzer Sinn was und wie könnte eine Lösung sein, wenn ich die Daten automatisch über „Daten“, „Text in Spalten“ aufteilen lasse, aber in der Feldreihenfolge dann doch wiedereine Ordnung rein bringen kann, hättest du da eine Idee?
Anzeige
Die einfachste Lösung wäre natürlich mit ...
23.11.2017 04:16:28
Luc:-?
…Text-in-Spalten, Siegfried,
und dann alles manuell zuordnen. Alternativ auch mit meiner UDF VSplit und der pluralen MatrixFml {=VSplit(C2:C1201;"; ")} über die erforderliche Spalten- und ZeilenAnzahl.
Allerdings habe ich mir deine Daten doch noch mal angesehen und festgestellt, dass eine Auswertung mit UDF-haltigen Fmln doch recht zielgenau erfolgen kann, weil die DatenSätze relativ regelmäßig sind, nur eben nicht vollständig. Aber das kann man einiger­maßen gut abfangen. Nur die Rubrik Straße sieht ziemlich wild aus und hat viele Lücken, während Ort immer vorhanden ist (falls das der richtige ist).
Ich gebe dir hier mal ein Bsp (die ersten 20 Zeilen der Tabelle):
 EFGHIJKLMN
1
PLZNameTelNrWertungSTP-AnzPreisOffenMailStraßeOrtHR-20250Camping Perna+385 207 192 862 (5)11027.40 €01.05.-30.09.kamp.perna@club-adriatic.hrPerna bbOrebicHR-21320Camping Basko Polje 3.3 (11) 22.00 €01.05.-31.10.kamp.baskopolje@club-adriatic.hr Baako PoljeHR-21334Camping Dalmacija+385 216 293 003.3 (3) 29.50 €01.03.-21.10.info@zaostrog.netHrvatskih Domoljuba bbZaostrogHR-21465Camping Grebisce+385 217 611 914.1 (15)15333.00 €01.05.-31.10.camp@grebisce.hr JelsaHR-23262Camping Lucina+385 232 601 73 50 01.06.-15.09.  PasmanHR-52212Camping Pineta+385 522 2318 843.6 (25) 28.60 €01.05.-30.09.pineta@brijunirivijera.hrPerojska Cesta bbFazanaHR-51559Camping Brajdi na Moru+385 518 405 22    branka.sare@ri.t-com.hr CresHR-51260Camping Kacjak+385 517 864 444 (1)   kacjak@jadran-crikvenica.hrDramaljCrikvenicaHR-51000Camping Preluk+385 516 235 002.4 (13)  15.04.-15.10.info@autocamppreluk.comPreluk bbRijekaHR-51250Camping Punta Povile+385 51 793 0833.7 (10) 24.00 €01.04.-30.09. Milana Butkovica 18Novi VinodolskiCZ-69301Autokemp Formanka Hustopece+420 739 376 0134 (2) 14.00 €01.01.-31.12.info@formanka.netSafaríkova 448/21HustopeceCZ-67913Camping Relaxa 1 (1)   camp.relaxa@seznam.cz SloupCZ-63500Autokemp Obora 2.5 (2)   info@autocampobora.czRakovecká 149/72BrnoCZ-66471Autokemp Oaza 4 (2)   info@kampoaza.net ChudciceCZ-69664Autocamping Lucina 5 (1)    Tvarozna' Lhota-lucinaKnezdubCZ-76326Autocamping Luhacovice 5 (1)   lubosnovosad@seznam.czVýsluní 400LuhacoviceCZ-38411Autocamp Podrouzek+420 388 324 4684 (2)10013.00 €01.05.-01.10.autocamp-podrouzek@c-box.czTyrsova 226NetoliceCZ-39301Camping Milos Valek+420 565 394 104 1210.00 €01.05.-31.08.svalkova@seznam.czMoravec 16PelhrimovCZ-38232Camping Guesthouse Rajka+420 380 743 8552.5 (2)2016.00 €01.06.-07.09.info@guesthouserajka.comMojné 7Mojné/Velesin
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Falls du daran Interesse hast, können wir wie folgt vorgehen:
• Variante1: Ich poste die gesamte Tabelle als HTML-Tabelle direkt im Beitrag oder als UpLoad, ggf auch eine .xlsx-Datei nur mit den Ergebnissen.
• Variante2: Falls du so etwas öfter machst und dich auch die Fmln interessieren, kann ich zusätzlich die Fmln der 2.Zeile posten und außerdem eine Liste mit Archiv- bzw DownLoad-Links zu den 6 verwendeten UDFs (CountOn, MaskOn, PickOn, Splint, SplitVx, VSplit)
• Variante3: Notfalls, wenn du mit Var2 nicht zurecht kommst, könnte ich dir auch die komplette Datei mit diesen UDFs als .xlsm hochladen, was ich ungern mache, weil ich dafür immer erst mein Xl temporär umstellen muss.
Falls du auf mein Angebot eingehen willst, kannst du mir hier auch nochmal antworten. Dazu gebe ich dir die ForumsAdresse deines letzten Beitrags (vom 22.11.) an (die von dem, den ich gerade schreibe, habe ich natürlich noch nicht!). Du hast dann bis mindes­tens 26.11. Zeit zu antworten, danach erreichst du die angegebene Adresse nicht mehr, aber die meines Beitrags 1 Tag länger, wenn du die ArchivAdresse in eine ForumsAdresse wandelst. Wir unterhalten uns dann (wahrscheinlich solo) im Hidden Forum. ;-)
Dein letzter Beitrag: https://www.herber.de/forum/messages/1593070.html
Morrn, Luc :-?
Anzeige
AW: Die einfachste Lösung wäre natürlich mit ...
25.11.2017 07:43:26
Siegfried
Hallo Luc,
ja würde gerne von deinem Angebot Gebrauch machen, wenn auch schon 73 man kann und soll ja immer was dazulernen.
Variante 2 wäre schon interessant!
Gruß Siegfried
Schön, dass du das noch gefunden hast, ...
26.11.2017 04:35:11
Luc:-?
…Siegfried; ;-)
Werner (neopa) übrigens auch, der dir hier jetzt ein ähnliches Angebot gemacht hat. Könntest du dann ja mal die Ergebnisse und Fmln vgln. Meine lauten wie folgt:
E2[:E1201]:=Splint(MaskOn(MaskOn(C2;"mrk[]");"anu";"-");;1;1)
F2[:F1201]:=PickOn(MaskOn(C2;"mrk[]";1);1;";")
G2[:G1201]:=WENNFEHLER(INDEX(VSplit(C2;"; ");VERGLEICH("+*";VSplit(C2;"; ");));"")
H2[:H1201]:=WENNFEHLER(Splint(INDEX(VSplit(C2;"; ");VERGLEICH("*wert*";VSplit(C2;"; ");));": ";2);"")
I2[:I1201]:=WENNFEHLER(--Splint(INDEX(VSplit(C2;"; ");VERGLEICH("*plätze*";VSplit(C2;"; ");));": ";2);"")
J2[:J1201]:=VSplit(WENNFEHLER(INDEX(VSplit(C2;"; ");VERGLEICH("*EUR (*)";VSplit(C2;"; ");));"");;1)
K2[:K1201]:=WECHSELN(WENNFEHLER(Splint(INDEX(VSplit(C2;"; ");VERGLEICH("*zeit*";VSplit(C2;"; ");));": ";2);"");" ";" & ")
L2[:L1201]:=WENNFEHLER(INDEX(VSplit(C2;"; ");VERGLEICH("*@*";VSplit(C2;"; ");));"")
M2[:M1201]:=WENN(CountOn(C2;"; ") N2[:N1201]:=MaskOn(INDEX(VSplit(GLÄTTEN(MaskOn(C2;"mrk[]"));" [");2);"]";1)
Die Links zu den Pgmm der 6 verwendeten UDFs:
CountOn (Vs1.1): https://www.herber.de/forum/archiv/732to736/732035_Zaehlennwenn_mit_Zahlenkombinationen.html#734566
MaskOn (Vs2.1): https://www.herber.de/cgi-bin/callthread.pl?index=1344962#1345181
PickOn (Vs1.1): https://www.herber.de/forum/archiv/1140to1144/1141994_Teilstring_aus_String_entfernen.html#1142025 (Folgebeiträge m.Korrekturen beachten!)
Splint (Vs1.2): https://www.herber.de/forum/archiv/1584to1588/t1584372.htm#1584465
SplitVx (Vs1.6): https://www.herber.de/cgi-bin/callthread.pl?index=1301785#1301987 (Thread)
- ~ - (BspDatei) https://www.herber.de/bbs/user/84157.xls (diese Vs ohne Arg4 enthält wie Vs2.0 noch einen kleinen Fehler)
VSplit (Vs1.1): https://www.herber.de/bbs/user/99024.xlsm (nur BspDatei mit UDF-Pgmm)
Falls du zukünftig, wenn du dich mit regulären Ausdrücken (regular expressions) auskennst, gern auch Ransis UDF verwenden möch­test, solltest du das in folgd Form tun: 
Rem Muster f.eMail-Adress: Arg2:= "\b(\w[-.\w]*@\w[-.\w]*\.[a-zA-Z]{2,6})\b"
Function TextFilter(Bezug, ByVal Muster As String)
Dim lngIndex As Long, M, Treffer, varTmp() As Variant, Regex As Object
On Error GoTo ex
Set Regex = CreateObject("Vbscript.regexp")
With Regex
.Pattern = Muster
.IgnoreCase = True
.Global = True
Set Treffer = .Execute(Bezug)
Redim varTmp(Treffer.Count - 1)
For Each M In Treffer
varTmp(lngIndex) = M.Value
lngIndex = lngIndex + 1
Next M
End With
ex: If CBool(Err.Number) Then
TextFilter = CVErr(xlErrNull)
Else: TextFilter = varTmp
End If
End Function
Dadurch kannst du unterschiedliche Muster als 2.Argument der UDF angeben. Das sollte man übrigens immer mit Werten (Variab­len) machen, die unterschiedlich benötigt wdn könnten. Nur unver­änderliche Konstanten kann man auch als Const und interne Variablen als Dim (oder ggf als Static) in der UDF deklarieren. Die Fml für L2ff würde mit dieser UDF dann hier so lauten:
=WENNFEHLER(TextFilter(C2;"\b(\w[-.\w]*@\w[-.\w]*\.[a-zA-Z]{2,6})\b");"")
Das vollständige Ergebnis habe ich als wg ihrer Größe „gezippte“ HTML-Datei hier hochgeladen (Link anklicken!). Der linke und obere Rand können beim Kopieren weggelassen wdn.
SchöSo! Morrn, Luc :-?
Anzeige
AW: ergänzend für Siegfried ...
27.11.2017 16:50:59
...
Hallo Siegfried,
... zum Vergleichen der Ergebnisse (dessen habe ich mir nicht angenommen) mit Deinen Wunschergebnissen und den Ergebnissen die Dir Luc übermittelt hat, hab ich in Deine ursprünglich eingestellte Datei, die ersten 498 Datensätze mit meinen Formellösungen bestückt und hier hochgeladen in: https://www.herber.de/bbs/user/117947.xlsx
Gruß Werner
.. , - ...
Anzeige
Habe diese Datei auf 1200 Zeilen vervollständigt …
29.11.2017 03:43:03
Luc:-?
…und mit meiner vgln, Werner;
dabei ergab sich, dass ich einige britische PLZ verstümmelt hatte (Leerzeichen in manchen PLZ nicht beachtet) und auch die StraßenListe nicht so vollständig hatte wie Du, wobei bei Dir auch einiges Überflüssige eingeht, was aber nicht weiter wild ist, da sofort erkennbar. Allerdings hat sich bei Dir in den Zeilen 97 und 1123 ein seltsamer Fehler eingeschlichen. In 1123 könnte ich das ja noch verstehen, denn es fehlt eine Ziffer am Ende (ist aber leider 'ne Hausnr!), in 97 aber mittendrin, aber wohl nur in einer zusätzlichen TelefonNr o.ä.
So, daraus ergeben sich 2 korrigierte Fmln in meiner Liste, Siegfried;
E2[:E1201]:=TEIL(Splint(MaskOn(C2;"mrk[]");"]";1;1);2;12)
M2[:M1201]:=WENN(CountOn(C2;"; ")<2;"";WENNFEHLER(T(1/(ANZAHL2(SplitVx(Rmd(PickOn(C2;CountOn(C2;"; ");"; "));
{": "."@"." ("};1))-1));WECHSELN(GLÄTTEN(INDEX(SplitVx(Rmd();"+";-1);1));"""";"")))
In M habe ich wg der Länge der Fml eine 7.UDF eingeführt, die das Anlegen benannter (Teil-)Fmln unter definierten Namen erspart. Falls du die nicht verwenden willst, musst du den entsprd (argumentierten!) Fml-Teil vom Einschluss durch Rmd(…) befreien und anstelle des Rmd() ohne Argument den befreiten Fml-Teil erneut notieren.
Anderenfalls kannst du diese UDF natürlich auch in den Fmln der Spalten G:L verwenden, was aber nicht unbedingt erforderlich wäre, aber dann so aussähe:
G2[:G1201]:=WENNFEHLER(INDEX(Rmd(VSplit(C2;"; "));VERGLEICH("+*";Rmd();));"")
H2[:H1201]:=WENNFEHLER(Splint(INDEX(Rmd(VSplit(C2;"; "));VERGLEICH("*wert*";Rmd();));": ";2);"")
I2[:I1201]:=WENNFEHLER(--Splint(INDEX(Rmd(VSplit(C2;"; "));VERGLEICH("*plätze*";Rmd();));": ";2);"")
J2[:J1201]:=VSplit(WENNFEHLER(INDEX(Rmd(VSplit(C2;"; "));VERGLEICH("*EUR (*)";Rmd();));"");;1)
K2[:K1201]:=WECHSELN(WENNFEHLER(Splint(INDEX(Rmd(VSplit(C2;"; "));VERGLEICH("*zeit*";Rmd();));": ";2);"");" ";" & ")
L2[:L1201]:=WENNFEHLER(INDEX(Rmd(VSplit(C2;"; "));VERGLEICH("*@*";Rmd();));"")
Das Pgm dieser UDF (Vs1.1) ist unter diesem Link zu finden.
Falls du uns noch 1× hier antworten willst, was nett wäre, kannst du das unter folgd Link zu Werners letztem Beitrag tun: https://www.herber.de/forum/messages/1593976.html
Morrn, Luc :-?
Anzeige
AW: Habe diese Datei auf 1200 Zeilen vervollständigt …
29.11.2017 08:54:40
Siegfried
Hallo Werner und Luc,
also ihr zwei gefällt mir, macht euch mit meinem Hilferuf gegenseitig Testabläufe welches eurer Lösungen die bessere wäre. Für mich als nicht vbln und sonstiger Formelspezialist Großteils ein spanisches Dorf.
Das dauert noch bis ich das umgesetzt habe und eventuell dann auch etwas davon verstanden habe.
Da mir leider immer wieder Daten unterkommen in welchen keine „,“ oder „;“ oder sonstige Trennzeichen gesetzt sind, da das betreffende Datenfeld leer ist, stehe ich eben immer wieder vor neuen Herausforderungen dieses zu lösen.
Da gefällt mir der Excel-Menüpunkt „Daten“ – „Text in Spalten“ als ersten Lösungsansatz sehr gut.
Erspare mir sämtliche Berechnungen der bereits erarbeiteten Feldlängen usw., usw. wie mir eure Formeln gezeigt haben.
Also wenn ich das erstellen müsste, dies würde bei mir Tage dauern bis ich die Formelbildung für einen Datensatz ohne FEHLER fertig hätte.
Die Frage ist nun wie könnte man nach Anwendung des Menüpunktes „Text in Spalten“ hier eine Ordnung in der Reihenfolge reinbringen. Die ersten beiden Datenfelder sind ja kein Problem. Wenn ich markante Inhalte wie, Gäste, EUR, Stellplatz, [Vbbnnm] usw. habe ist es auch leicht lösbar.
Wie könnte man es aber trotzdem lösen wenn mir solche markanten Suchinhalte fehlen?
Fällt Euch eventuell dazu was ein?
Gruß Siegfried
Anzeige
AW: da bleibt dann nur "Handarbeit" ... oder ...
29.11.2017 09:30:03
...
Hallo Siegfried,
... und die "Lösungsvariante" Handarbeit hatte ich gleich am Anfang dieses threads erwähnt. Wenn kein "markanter" Suchinhalt vorhanden ist, kann man eben auch keine logische Zuordnung vornehmen.
In Deiner Beispieldatei waren allerdings überall "markante" Suchinhalte vorhanden.
Wenn jedoch von Feldern ohne "markante" Suchinhalte bekannt sein sollte, dass diese immer genau 1 oder 2 Felder links/rechts von Feldern mit "markanten" Suchinhalten liegen sollten, dann kann man darüber eine z.B. formeltechnische Zuordnung vornehmen.
Gruß Werner
.. , - ...
Anzeige
Handarbeit hatte ich auch zuvor schon erwähnt, …
29.11.2017 21:56:45
Luc:-?
…Siegfried
(s. Die einfachste Lösung …). Ansonsten kann ich mich nur Werner anschließen.
Viel Erfolg! Ciao, Luc :-?
AW: nun ...
25.11.2017 09:52:45
...
Hallo Siegfried,
... dann wünsche ich Dir zunächst gute Erholung.
Ich bin jetzt erst über Luc´s Antwort auf Deinen Beitrag vom 22.11.2017 19:41:39 gestoßen. Da der thread im Forum schon nicht mehr aktiv gelistet wird und ich keine automatische Mailbenachrichtigung eingerichtet habe, war mir Dein Beitrag zunächst "durch die Lappen gegangen".
Ich kann nur feststellen, dass ich Deine Beispieldaten mit meinen Forrmelansätzen komplett "gewandelt" habe. Wo gab es denn bei Dir damit noch Probleme? Dann stell eine entsprechende Datei noch mal zur Verfügung.
Gruß Werner
.. , - ...
Anzeige
AW: das wird dann so nur schwer möglich sein, ...
19.11.2017 00:50:49
Siegfried
Hier bitte eine Musterdatei meines Problems, DANKE für eine brauchbare Lösung!
Gruß Siegfried
Da meine AWen komplett ignoriert bzw ...
17.11.2017 18:21:53
Luc:-?
…kaum beachtet geschweige denn erwähnt wdn, sage ich dazu nichts. Pfriemel ruhig weiter! :-[
Luc :-?
AW: Da meine AWen komplett ignoriert bzw ...
17.11.2017 23:05:46
Siegfried
Hallo Luc,
tut mir leid aber ich bin nicht so oft hier im Forum, habe die verschiedenen Anregungen immer gleich versucht, kann daher jetzt nicht mehr genau sagen was da nicht funktionierte, entschuldige bitte daher.
Gruß Siegfried
Anzeige

Forumthreads zu verwandten Themen

Userform Textboxinhalte in Zellen schreiben
Gerd

Guten Tag zusammen!
Bitte helft mir bei folgendem Problem:
In einer Userform habe ich eine ganze Reihe Textboxen, in die zum größten Teil Zahlen eingegeben werden.
Nun ist ja bekannt, dass beim Schreiben in Zellen der Inhalt der Textboxen als Text interpretiert wird. Mit der Anweisu...
11.08.2008 14:33:59
Thread-ID: 1000866
3 Beiträge
Userform Textboxinhalte in Zellen schreiben
Gerd

Guten Tag zusammen!
Bitte helft mir bei folgendem Problem:
In einer Userform habe ich eine ganze Reihe Textboxen, in die zum größten Teil Zahlen eingegeben werden.
Nun ist ja bekannt, dass beim Schreiben in Zellen der Inhalt der Textboxen als Text interpretiert wird. Mit der Anweisu...
11.08.2008 14:34:08
Thread-ID: 1000867
5 Beiträge
Zeilen in Zellen suchen und löschen
Daniel

Hallo,
ich möchte in Excell mehrere Zelle suchen, die folgenden String in einer oder mehreren Zeilen enthalten: ----
Diese Zeilen möchte ich dann löschen. Nach diesem String sind noch weitere Zeichen vorhanden, die auch gelöscht werden sollen.
Weiß jemand wie ich am besten vorgehe?...
13.08.2008 12:52:02
Thread-ID: 1001495
9 Beiträge
Wert in farbiger Zelle addieren
thomas

Hallo,
ich möchte den Wert in farbigen Zellen addieren.
Z.B.
Zelle A1 :C1 Zellfarbe gelb, in jeder Zelle steht der Wert 1 ; Ergebnis soll 3 sein
oder
Zelle A2 = grün Wert =1, B2 = gelb Wert=2, C2 = gelb Wert =2; Ergebnis soll 4 sein
gibt es dafür eine Funktion oder is...
13.08.2008 12:56:00
Thread-ID: 1001497
4 Beiträge
VBA - Zelle kopieren bis Wert einer Zelle null ist
Schulze

Halli Hallo,
ich bin ziemlicher Neuling in VBA. Daher würde ich euch bitten mir bei meiner Fragestellung zu helfen und zwar wie folgt: Ich möchte aus einer Tabelle aller Zellen kopieren, bis der Wert einer Zelle 0 annimmt. Diesen bestimten Bereich möchte ich in einer neuen Datei abspeichern...
19.08.2008 08:39:35
Thread-ID: 1002782
17 Beiträge
unterschiedliche Buchstaben in Textzellen finden
Rob

Liebe xls-Profis,
ich muss Kunden-Stammdaten von 2 Datenbanken vergleichen u habe folgende Aufgabe:
2 Textzellen miteinander vergleichen und Unterschiede finden und markieren.
Einfache Beispiele:
A1 B1
Huber Hubber hier sollte in der Zelle B1 z.B. mit be...
20.08.2008 10:26:10
Thread-ID: 1003114
8 Beiträge
Textteile in Zellen mit Liste vergleichen
susidimarca

Hallo,
ich habe derzeit ein kniffeliges Excelproblem.
ich möchte gerne den Text in Zellen
hjgshgjdf Kriterium2 kjhfkjhfksdjhf
dskj Kriterium1 ajdhasjdhakjdhakdhajkdhajkdakj
daudzauizdaiudzaidzaisdz Kriterium3 kajhdasks
mit den Kriterium eine anderen Tabelle vergleichen...
21.08.2008 10:05:00
Thread-ID: 1003378
6 Beiträge
Anzeige
Suchfunktion - TextBox kein Text sondern Datum
Frank

Hallo Liebe Excelgemeinde!!!
In einer UserForm befindet sich folgender Code um bestimmte Einträge zu suchen. Da in der TextBox1 aber kein Text sondern ein Datum steht funktioniert dies nicht, da kommt die Meldung " Suchbegriff wurde nicht gefunden".
Was muss ich ändern, damit auch nach...
21.08.2008 15:35:00
Thread-ID: 1003541
4 Beiträge
Wenn nur Text in Zelle...
MichiM

Hallo,
ich habe eine umfangreiche Artikelliste (ca. 250.000 Artikel) aus einer Datanormdatei erstellt.
Aus dieser muss ich jetzt alle Artikel entfernen, dessen Artikelnummer (in Spalte A; als Text formatiert, damit führende Nullen nicht gelöscht werden) keinerlei Zahlen enthalten.
E...
22.08.2008 16:24:44
Thread-ID: 1003960
4 Beiträge
Hintergrundfärbung in UserForm Textbox in Zelle...
Josef

Guten Morgen!
wie ich Daten über Userform TextBoxen in die Zellen zurückschreibe, dafür habe ich bereits eine Lösung.
Is es jedoch möglich einen gefärbten Hintergrund einer TextBox in die dafür vorgesehene Zelle zu übertragen?
Wenn ja, wie würde dann bitte eine VBA Lösung lauten?
28.08.2008 06:57:47
Thread-ID: 1005257
10 Beiträge
Zellen ausblenden mit Wert 0 in Zelle
Ulrich

Hallo zusammen,
wie kann ich einem Tabellenblatt alle Zeilen ausblenden, bei denen in der ersten Zelle eine 0 (null) steht? Geht sowas überhaupt?
Viele Grüße,
Ulrich
01.09.2008 11:08:49
Thread-ID: 1006360
6 Beiträge
Zelleninhalt (Text) in 2 Spalten teilen
El.

Hallo!
Habe eine Spalte in der Artikelname und Artikelmerkmale, durch drei Punkte getrennt "...", zusammen stehen. Beispiel: Lecker Whisky 21 Jahre ... Lieferung in hochwertiger Schatulle
Wie krieg ich nun den Text links der Punkte in eine extra Spalte und rechts der Punkte in eine extr...
03.09.2008 16:35:00
Thread-ID: 1007155
4 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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