21
Excel 2003 - Name, Vorname, Titel und Beruf in einer Zelle auf mehrere Zellen aufteilen
Hallo ihr alle!
Folgendes Problem stellt sich mir:
In einer sehr langen Exceltabelle steht in einer Zelle immer Name, Vorname, Beruf, Titel in verschiedener Länge und Reihenfolge, manchmal fehlt auch eine Angabe.
Das Ziel wäre, dass Name, Vorname, Titel und Beruf in je einer eigenen Zelle geschrieben steht.
Hierzu habe ich in einem zweiten Tabellenblatt je eine Spalte mit Vornamen (Spalte C),
Titel (Spalte D)
und Berufe (Spalte E) angelegt.
Beispiel:
Im Tabellenblatt1 (Blatt1) steht in Spalte A , Name Vorname Beruf Titel
Im Tabellenblatt2 (Blatt2) stehen in Spalte C die möglichen Vornamen (Anna ... Zenta),
in Spalte D die möglichen Titel (Dr. ... Prof.)
und in Spalte E die möglichen Berufe (Angler ... Zeppellinfahrer)
Ausgabe:
Tabellenblatt1 (Blatt1) Spalte B: "Name"
Spalte C: "Vorname"
Spalte D: "Titel"
Spalte E: "Beruf"
Es müsste also jeweils geschaut werden, ob der Vorname, Beruf und Titel in der Liste im Tabellenblatt2 enthalten ist, wenn ja solllte er in der entsprechenden Spalte im Tabellenblat1 1 geschrieben werden, wenn kein passender eintrag vorhanden ist bleibt die Zelle leer.
Der Name in Spalte B ergäbe sich dann aus
Inhalt B = (Inhalt A - Inhalt C - Inhalt D - Inhalt E)
Hat jemand eine Lösung, bzw. wer kann helfen.
Vielen herzlichen Dank euer Excel-Starter
Folgendes Problem stellt sich mir:
In einer sehr langen Exceltabelle steht in einer Zelle immer Name, Vorname, Beruf, Titel in verschiedener Länge und Reihenfolge, manchmal fehlt auch eine Angabe.
Das Ziel wäre, dass Name, Vorname, Titel und Beruf in je einer eigenen Zelle geschrieben steht.
Hierzu habe ich in einem zweiten Tabellenblatt je eine Spalte mit Vornamen (Spalte C),
Titel (Spalte D)
und Berufe (Spalte E) angelegt.
Beispiel:
Im Tabellenblatt1 (Blatt1) steht in Spalte A , Name Vorname Beruf Titel
Im Tabellenblatt2 (Blatt2) stehen in Spalte C die möglichen Vornamen (Anna ... Zenta),
in Spalte D die möglichen Titel (Dr. ... Prof.)
und in Spalte E die möglichen Berufe (Angler ... Zeppellinfahrer)
Ausgabe:
Tabellenblatt1 (Blatt1) Spalte B: "Name"
Spalte C: "Vorname"
Spalte D: "Titel"
Spalte E: "Beruf"
Es müsste also jeweils geschaut werden, ob der Vorname, Beruf und Titel in der Liste im Tabellenblatt2 enthalten ist, wenn ja solllte er in der entsprechenden Spalte im Tabellenblat1 1 geschrieben werden, wenn kein passender eintrag vorhanden ist bleibt die Zelle leer.
Der Name in Spalte B ergäbe sich dann aus
Inhalt B = (Inhalt A - Inhalt C - Inhalt D - Inhalt E)
Hat jemand eine Lösung, bzw. wer kann helfen.
Vielen herzlichen Dank euer Excel-Starter
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 171418
Url: https://administrator.de/contentid/171418
Printed on: April 19, 2024 at 08:04 o'clock
21 Comments
Latest comment
Hallo Excel-Starter!
Ich frag' mal gar nicht, wie es zu dem Kudelmuddel kommen konnte ...
... aber gibt es noch nicht einmal ein (zuverlässiges) Trennzeichen zwischen den einzelnen Werten, sodass wenigstens per "Text in Spalten" eine Zerlegung vorweg erfolgen könnte?
Anyhow:
Wenn Du Dein Vorhaben wie beschrieben umsetzen willst, dann am ehesten noch mit ein wenig VBA:
Grüße
bastla
Ich frag' mal gar nicht, wie es zu dem Kudelmuddel kommen konnte ...
... aber gibt es noch nicht einmal ein (zuverlässiges) Trennzeichen zwischen den einzelnen Werten, sodass wenigstens per "Text in Spalten" eine Zerlegung vorweg erfolgen könnte?
Anyhow:
Wenn Du Dein Vorhaben wie beschrieben umsetzen willst, dann am ehesten noch mit ein wenig VBA:
- Rufe mit Alt-F11 den VBA-Editor auf
- Erstelle über "Einfügen" ein "Modul"
- Kopiere den folgenden Code in das große Bearbeitungsfenster:
Function Enthalten(Liste As Range, Wert As String) As String
For Each Eintrag In Liste.Cells
If InStr(Wert, " " & Eintrag & " ") Then
Enthalten = Eintrag
Exit For
ElseIf LCase(Right(Wert, Len(Eintrag))) = LCase(Eintrag) Then
Enthalten = Eintrag
Exit For
End If
Next
End Function- Für C1 (Ermittlung des Vornamens) kannst Du jetzt (unter der Annahme, dass in den Zellen C1:C500 der "Tabelle2" Deine Namensliste steht) folgende Formel verwenden:
=Enthalten(Tabelle2!$C$1:$C$500;A1)
- Analog die Vorgangsweise für Titel und Beruf, wobei in allen Fällen genaue Übereinstimmung (auch Groß-/Kleinschreibung) geprüft wird
- Zuletzt kannst Du in Spalte B1 zur Ermittlung des Namens die folgende Formel versuchen:
=GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(A1;C1;"");D1;"");E1;""))
bastla
Hallo Bastla,
vielen Dank für Deine (erste) Antwort. "Text in Spalten" funktioniert schon, da zumindest immer ein Leerzeichen vorhanden ist. Nur dann steht auf Grund der nicht einheitlichen Reihenfolge und der verschienden Anzahl der Einträge, das Gemenge in mehreren Spalten in verschiedener Anzahl. Hilft dies weiter?
Euer Excel-Starter
vielen Dank für Deine (erste) Antwort. "Text in Spalten" funktioniert schon, da zumindest immer ein Leerzeichen vorhanden ist. Nur dann steht auf Grund der nicht einheitlichen Reihenfolge und der verschienden Anzahl der Einträge, das Gemenge in mehreren Spalten in verschiedener Anzahl. Hilft dies weiter?
Euer Excel-Starter
Hallo Excel-Starter!
Ich habe mich zwischenzeitlich an einer VBA-Lösung versucht ...
... ansonsten könntest Du eigentlich nur für jeden Einzelwert (hier am Beispiel der ersten Zeile mit Werten in A1:D1) mit je 3 Spalten (zB ab F1)
versuchen, einen übereinstimmenden Eintrag in der "Tabelle2" zu finden.
Weiter ginge es dann zB ab J1 mit
und ab N1 mit
Am Ende könntest Du dann die gefundenen Werte (zB Prüfung auf "Vorname" in den Spalten F, J und N) in einer (endgültigen) Vornamenspalte zusammenfassen:
Dieser Ansatz ist allerdings gänzlich ungetestet und geht davon aus, dass der Name immer in der Spalte A steht ...
Grüße
bastla
Ich habe mich zwischenzeitlich an einer VBA-Lösung versucht ...
... ansonsten könntest Du eigentlich nur für jeden Einzelwert (hier am Beispiel der ersten Zeile mit Werten in A1:D1) mit je 3 Spalten (zB ab F1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$C$1:$C$500;1;0));"";B1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$D$1:$D$20;1;0));"";B1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$E$1:$E$300;1;0));"";B1) Weiter ginge es dann zB ab J1 mit
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$C$1:$C$500;1;0));"";C1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$D$1:$D$20;1;0));"";C1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$E$1:$E$300;1;0));"";C1) =WENN(ISTNV(SVERWEIS(A2;Tabelle2!$C$1:$C$500;1;0));"";D1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$D$1:$D$20;1;0));"";D1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$E$1:$E$300;1;0));"";D1) =F1&J1&N1
Grüße
bastla
Hallo,
Und was ist mit Heinz Willy Mustermann?
Woher soll der Computer wissen was ein Vorname ist? Das fällt ja sogar Menschen manchmal schwer.
Diese Listen wird Jemand von Hand sortieren oder zumindest kontrollieren müssen.
Stefan
Und was ist mit Heinz Willy Mustermann?
Woher soll der Computer wissen was ein Vorname ist? Das fällt ja sogar Menschen manchmal schwer.
Diese Listen wird Jemand von Hand sortieren oder zumindest kontrollieren müssen.
Stefan
Hallo Bastla,
ich habe zwischenzeitlich Deine erste Lösung probiert, aber es funktioniert nur sehr bedingt.
Als Vorname wird immer der erste Eintrag aus der Liste verwendet, auch wenn er ein Teil eines Wortes darstellt.
Beispiel A1 "Anna Michaelik"
In der Vornamensliste gibt es einen Eintrag "Elik", diesen nimmt er aus dem Ende von "Michaelik", in " B" wird "Anna Michaelik" geschrieben
Wenn der echte Vorname der erste Name in der Vornamensliste ist, dann funktioniert es, wenn er als ganzer Name erkannt wird er auch in " B " abgezogen
Franz Meier -> funktioniert
Gibt es eine Möglichkeit, dass nur nach ganzen einzeln vorhandenen Namen gesucht wird?
Danke und viele Grüße euer Excel Starter
Tabelle1 A1 steht das erste Namensgemenge, A2 das nächste, A3, ....
Tabelle2 SpalteC stehen die Vornamen
ich habe zwischenzeitlich Deine erste Lösung probiert, aber es funktioniert nur sehr bedingt.
Als Vorname wird immer der erste Eintrag aus der Liste verwendet, auch wenn er ein Teil eines Wortes darstellt.
Beispiel A1 "Anna Michaelik"
In der Vornamensliste gibt es einen Eintrag "Elik", diesen nimmt er aus dem Ende von "Michaelik", in " B" wird "Anna Michaelik" geschrieben
Wenn der echte Vorname der erste Name in der Vornamensliste ist, dann funktioniert es, wenn er als ganzer Name erkannt wird er auch in " B " abgezogen
Franz Meier -> funktioniert
Gibt es eine Möglichkeit, dass nur nach ganzen einzeln vorhandenen Namen gesucht wird?
Danke und viele Grüße euer Excel Starter
Tabelle1 A1 steht das erste Namensgemenge, A2 das nächste, A3, ....
Tabelle2 SpalteC stehen die Vornamen
Hallo Stefan,
bei "Heinz Willy Mustermann" erkennt er "Ann" als Vorname, weil dies anscheinend der erste "Vorname" ist, den er als Buschstabenfolge in "Heinz Willy Mustermann" erkennt.
Wenn man die Suche auf ganze Wörter einschränken könnte, so müsste "Heinz" und "Willy" als Vorname erkannt werden.
Viele Grüße
Excel Sarter
bei "Heinz Willy Mustermann" erkennt er "Ann" als Vorname, weil dies anscheinend der erste "Vorname" ist, den er als Buschstabenfolge in "Heinz Willy Mustermann" erkennt.
Wenn man die Suche auf ganze Wörter einschränken könnte, so müsste "Heinz" und "Willy" als Vorname erkannt werden.
Viele Grüße
Excel Sarter
Hallo Excel-Starter!
Einerseits war ich, wie schon erwähnt, davon ausgegangen, dass an erster Stelle der Zuname steht - daher wird dort auch nicht gesucht - teilweise Übereinstimmungen (wie in Deinem Beispiel "Elik" oder auch "Ann") werden nur am Ende des Eintrages gefunden - ansonsten wird immer nach ganzen Wörtern (= durch Leerzeichen begrenzt) gesucht ...
Nachdem die Zerlegung aber schon mal erfolgt ist, könntest Du es ja mit dem anderen Ansatz versuchen ...
Grüße
bastla
Einerseits war ich, wie schon erwähnt, davon ausgegangen, dass an erster Stelle der Zuname steht - daher wird dort auch nicht gesucht - teilweise Übereinstimmungen (wie in Deinem Beispiel "Elik" oder auch "Ann") werden nur am Ende des Eintrages gefunden - ansonsten wird immer nach ganzen Wörtern (= durch Leerzeichen begrenzt) gesucht ...
Nachdem die Zerlegung aber schon mal erfolgt ist, könntest Du es ja mit dem anderen Ansatz versuchen ...
Grüße
bastla
Hallo Excel-Starter!
Grüße
bastla
weil dies anscheinend der erste "Vorname" ist, den er als Buschstabenfolge in "Heinz Willy Mustermann" erkennt.
Eigentlich war die VBA-Variante für das komplette "Namensgemenge" (inkl Titel und Beruf) gedacht - und dass dort der Name am Ende steht, hatte ich nicht angenommen ...Grüße
bastla
Zitat von @Excel-Starter:
Wenn man die Suche auf ganze Wörter einschränken könnte, so müsste "Heinz" und "Willy" als
Vorname erkannt werden.
Aber Willy kann ja auch ein Nachname sein.Wenn man die Suche auf ganze Wörter einschränken könnte, so müsste "Heinz" und "Willy" als
Vorname erkannt werden.
Hallo StefanKittel!
Bei dieser "Gemengelage" wird vermutlich eine richtige Zuordnung von 80 % der Namen schon ein Erfolg sein ...
Wie würde denn Dein Vorschlag aussehen?
Grüße
bastla
Bei dieser "Gemengelage" wird vermutlich eine richtige Zuordnung von 80 % der Namen schon ein Erfolg sein ...
Wie würde denn Dein Vorschlag aussehen?
Grüße
bastla
Hallo,
da aufgrund der Datenlage eine automatische Zuordnunge unmöglich ist habe ich mich vornehm damit zurückgehalten
Zuerst würde ich schaue wieviele Einträge ich pro Minute manuell zuordnen kann und den Zeitaufwand hochrechnen.
Wenn da eine Zahl <2 Stunden rauskommt so machen und fertig.
Wenn ich diese Sch... verbockt hätte wäre mein Plan folgender.
- Wir brauchen 2 Makros
- Aufteilen der einzelnen Worte in jeweils eine Spalte (gibts auch ne VB Funktion für)
- Suchen nach Begriffen aus einer anderen Spalte und zuordnen zu einer Kathegorie
(Also eine Liste mit 100 Berufen über alle Einträge suchen, bei einem Treffer den Eintrag in die Spalte Beruf verschieben)
- Die Spalte Titel sollte einfach zuzuordnen sein
- Versuschen die Spalte Beruf zuzuordnen. Vieleicht gibts ja nicht so viele
Erstmal 50 Beruf manuell raussuchen und zuordnen lassen
Vieleicht gibts im Internet ja Liste dafür die man verwenden kann? Von den Genossenschaften z.B.
- Das gleiche mit der Spalte Vorname
- Dann die verbleibenen nach weiteren Vornamen und Beruf durchschauen und zuordnen lassen
Zum schluss bleiben geschätzt 10%-20% Berufe und Vornamen die nur einzelnd vorkommen und manuell zugeordnet werden müssen.
Alles in allem ein Sch...Job.
Ich habe früher häufiger für einen Kundenlisten sortiert, abgeglichen, aufgeteilt und konvertiert.
Aus allen möglichen und unmöglichen Formaten. Teilweise mehrere Millionen Einträge. Abfragen dauerten nicht unhäufig mehrere Stunden mit Access oder SQL.
Das erinnet mich an ein Puzzle was ich mal hatte:
- Das Bild zeigt gezeichnete Regentropfen in Schwarz/Weiß
- Alle Puzzleteile hatten die gleiche Form
- Das Puzzle war beidseitig
- Das Puzzle hatte 1.000 Teile
Viel Erfolg
PS: Versuch die Aufgabe loszuwerden
da aufgrund der Datenlage eine automatische Zuordnunge unmöglich ist habe ich mich vornehm damit zurückgehalten
Zuerst würde ich schaue wieviele Einträge ich pro Minute manuell zuordnen kann und den Zeitaufwand hochrechnen.
Wenn da eine Zahl <2 Stunden rauskommt so machen und fertig.
Wenn ich diese Sch... verbockt hätte wäre mein Plan folgender.
- Wir brauchen 2 Makros
- Aufteilen der einzelnen Worte in jeweils eine Spalte (gibts auch ne VB Funktion für)
- Suchen nach Begriffen aus einer anderen Spalte und zuordnen zu einer Kathegorie
(Also eine Liste mit 100 Berufen über alle Einträge suchen, bei einem Treffer den Eintrag in die Spalte Beruf verschieben)
- Die Spalte Titel sollte einfach zuzuordnen sein
- Versuschen die Spalte Beruf zuzuordnen. Vieleicht gibts ja nicht so viele
Erstmal 50 Beruf manuell raussuchen und zuordnen lassen
Vieleicht gibts im Internet ja Liste dafür die man verwenden kann? Von den Genossenschaften z.B.
- Das gleiche mit der Spalte Vorname
- Dann die verbleibenen nach weiteren Vornamen und Beruf durchschauen und zuordnen lassen
Zum schluss bleiben geschätzt 10%-20% Berufe und Vornamen die nur einzelnd vorkommen und manuell zugeordnet werden müssen.
Alles in allem ein Sch...Job.
Ich habe früher häufiger für einen Kundenlisten sortiert, abgeglichen, aufgeteilt und konvertiert.
Aus allen möglichen und unmöglichen Formaten. Teilweise mehrere Millionen Einträge. Abfragen dauerten nicht unhäufig mehrere Stunden mit Access oder SQL.
Das erinnet mich an ein Puzzle was ich mal hatte:
- Das Bild zeigt gezeichnete Regentropfen in Schwarz/Weiß
- Alle Puzzleteile hatten die gleiche Form
- Das Puzzle war beidseitig
- Das Puzzle hatte 1.000 Teile
Viel Erfolg
PS: Versuch die Aufgabe loszuwerden
Hallo StefanKittel!
Grundsätzlich habe ich das auch so ähnlich gesehen, aber ein gewisser Teil sollte sich doch automatisieren lassen - das war auch die Idee hinter meinem Vorschlag ...
Soweit zur Theorie - ansonsten schaun mer mal ...
Grüße
bastla
Grundsätzlich habe ich das auch so ähnlich gesehen, aber ein gewisser Teil sollte sich doch automatisieren lassen - das war auch die Idee hinter meinem Vorschlag ...
- Aufteilen der einzelnen Worte in jeweils eine Spalte (gibts auch ne VB Funktion für)
Ist ja schon mal mit "Text in Spalten" passiert.- Suchen nach Begriffen aus einer anderen Spalte und zuordnen zu einer Kathegorie
Die Kategorie lässt sich ja offensichtlich (wegen der nicht einheitlichen Reihenfolge) nicht ohne Weiteres finden - daher auch für jeden einzelnen entstandenen Teil die Suche (per SVERWEIS) in allen 3 Kategorien - wenn nicht gefunden, wird in die Zelle ein Leerstring geschrieben, ansonsten der Wert übernommen; durch das Zusammenfassen der jeweilgen "Kategoriespalten" ergibt sich dann (hofffentlich) der passende Eintrag ...Soweit zur Theorie - ansonsten schaun mer mal ...
Grüße
bastla
[OT]
Ich sach ma' so...
Die Produktstrategen von Sheba versuchen auch, etwas lieblos Zusammengekehrtes ansprechend aufzupeppen.
Die garnieren ihren Manschmatsch mit einem kleinen Petersiliensträusschen.
Die eigentliche Parallele zu diesem Beitrag ist allerdings: Es ist alles für die Katz.
@Excel-Starter
Es muss nicht immer ein Zeichen von Kompetenz sein, jede Aufgabe auch zu lösen.
Auch Ablehnen kann professionell sein.
Bei vernünftiger und nachvollziehbarer Begründung wird dir auch kein Cheffe den Kopf abreissen, wenn du sagst:
Ich schliesse mich weitgehend StefanKittels Bewertung an - wenn es denn auf jeden Fall gemacht werden soll trotz der -hey! Unter Brüdern dürftigen Basisdaten-Situation - dann geht von einer hauptsächlich bio-optischen, manuellen Sortiererei aus.
Dann holt euch halt ein paar ferienjobbende Neuntklässler, für die 4 Euro/Stunde ein Haufen Kohle ist und schliesst die zwei Tage ein mit dem Mist.
Alle drei, vier Stunden mal ne Pizza unter der Tür durchschieben und eine Böhse-Onkelz-CD als Taktgeber, dann machen die das.
Grüße
Biber
[OT]
Ich sach ma' so...
Die Produktstrategen von Sheba versuchen auch, etwas lieblos Zusammengekehrtes ansprechend aufzupeppen.
Die garnieren ihren Manschmatsch mit einem kleinen Petersiliensträusschen.
Die eigentliche Parallele zu diesem Beitrag ist allerdings: Es ist alles für die Katz.
@Excel-Starter
Es muss nicht immer ein Zeichen von Kompetenz sein, jede Aufgabe auch zu lösen.
Auch Ablehnen kann professionell sein.
Bei vernünftiger und nachvollziehbarer Begründung wird dir auch kein Cheffe den Kopf abreissen, wenn du sagst:
"Diese Aufgabe ist nicht mit akzeptablen Erfolgsaussichten automatisiert zu lösen, weil Input unbrauchbar, weil zu fehlerträchtig, weil dennoch hohe manuelle Nacharbeit erforderlich wäre etc."
Ich schliesse mich weitgehend StefanKittels Bewertung an - wenn es denn auf jeden Fall gemacht werden soll trotz der -hey! Unter Brüdern dürftigen Basisdaten-Situation - dann geht von einer hauptsächlich bio-optischen, manuellen Sortiererei aus.
Dann holt euch halt ein paar ferienjobbende Neuntklässler, für die 4 Euro/Stunde ein Haufen Kohle ist und schliesst die zwei Tage ein mit dem Mist.
Alle drei, vier Stunden mal ne Pizza unter der Tür durchschieben und eine Böhse-Onkelz-CD als Taktgeber, dann machen die das.
Grüße
Biber
[OT]
Guten Morgen werte Mitstreiter,
zunächst vielen Dank für eure Mühe. Zum "Namensgemenge" kann ich nur sagen, ich war es nicht.
Die Neuntklässler sind auch nicht die Lösung, es handelt sich so zusammengezählt um ca 180.000 Zellen mit "Namensgemenge", aufgeteilt in mehre Dateien. ... und die Pizza müsste ich wohl selber zahlen.
Wenn 80% passen würde, wäre es ja schon mal ein Erfolg
Es müsste aus dem Namensgemenge (Name, Vorname, Titel, Beruf - in unterschiedlicher Reihefolge) wenigstens Vorname und Titel autimatisch rausgeholt werden, dann bleibt noch Name uns Beruf.
Für Name gibt es keine Liste, die Berufsliste ist zwar lang aber hat deutlich wenig Trefferqualität als Vorname.
Aber am Ende sollte halt Titel, Vorname, Name, Beruf so weit wie möglich getrennt stehen.
Ich habe den zweiten Lösungsansatz von Bastla versucht (SVerweis), aber entweder habe ich es nicht richtig umgesetzt, oder es funktioniert nicht.
Vielleicht könnt ihr mir nochmals helfen
Danke und viele Grüße
Excel Starter
zunächst vielen Dank für eure Mühe. Zum "Namensgemenge" kann ich nur sagen, ich war es nicht.
Die Neuntklässler sind auch nicht die Lösung, es handelt sich so zusammengezählt um ca 180.000 Zellen mit "Namensgemenge", aufgeteilt in mehre Dateien. ... und die Pizza müsste ich wohl selber zahlen.
Wenn 80% passen würde, wäre es ja schon mal ein Erfolg
Es müsste aus dem Namensgemenge (Name, Vorname, Titel, Beruf - in unterschiedlicher Reihefolge) wenigstens Vorname und Titel autimatisch rausgeholt werden, dann bleibt noch Name uns Beruf.
Für Name gibt es keine Liste, die Berufsliste ist zwar lang aber hat deutlich wenig Trefferqualität als Vorname.
Aber am Ende sollte halt Titel, Vorname, Name, Beruf so weit wie möglich getrennt stehen.
Ich habe den zweiten Lösungsansatz von Bastla versucht (SVerweis), aber entweder habe ich es nicht richtig umgesetzt, oder es funktioniert nicht.
Vielleicht könnt ihr mir nochmals helfen
Danke und viele Grüße
Excel Starter
Hallo Excel-Starter!
Nochmals zum zweiten Ansatz:
[Edit] Vor dem Zerlegen der Werte in der Spalte A durch "Text in Spalten" die gesamte Spalte A in die Spalte V kopieren! [/Edit]
Nachdem per "Text in Spalten" die Daten aus Spalte A aufgeteilt wurden (es sollte jetzt in den Spalten A bis zumindest D einzelne "Wörter" geben), wäre für jede der Ergebnisspalten nach 3 Kriterien zu suchen: Vorname, Titel und Beruf. In meinem Beispiel oben hatte ich angenommen, dass alle Vornamen in "Tabelle2!C1:C500" stünden - speziell die Adresse der letzten Zelle musst Du natürlich anpassen. Genauso habe ich für die Titel den Bereich "Tabelle2!D1:D20" und für die Berufe "Tabelle2!E1:E300" angenommen - auch hier sind Anpassungen erforderlich.
Unter der Annahme, dass die Spalte F keine Daten mehr enthält, könntest Du (anders als oben angedacht und damit hoffentlich übersichtlicher) dort damit beginnen, den Vornamen zu suchen - daher in F1 bis zumindest I1 die Formeln
eintragen (und natürlich dann nach unten ausfüllen) - ich bin jetzt davon ausgegangen, dass auch die Spalte A bereits den Vornamen enthalten könnte.
Mit diesen Formeln wird versucht, die Wörter in A1, B1, C1 und D1 im "Vornamen"-Bereich "Tabelle2!$C$1:$C$500" (Absolutbezug ist wichtig) zu finden; wird der Name gefunden, wird die Originalschreibweise lt A1, B1, C1 oder D1 verwendet, ansonsten steht "" = "Nichts" in der Zelle. Falls (was zu erhoffen ist) nur genau eine der Zellen A1, B1, C1 und D1 einen Vornamen enthalten hat, befinden sich zB für den Fall, dass der Vorname in C1 steht, in den Zellen F1 und G1 der Wert "" (also "Nichts"), in H1 der Vorname und in I1 wieder "Nichts" - es ließe sich daher in zB der Zelle X1 die Formel
dazu verwenden, die Inhalte aller 4 Zellen zusammenzufassen, wodurch dort dann tatsächlich nur noch der Vorname (aus H1) stünde.
Analog dazu könnten ab zB K1 die Spalten für den Titel folgen, und die Zusammenfassung
könntest Du in Y1 vornehmen.
Dann nochmals das Ganze für zB P1 bis S1 mit Zusammenfassung in Z1.
Was bliebe, wäre das "Subtrahieren" der Inhalte von X1 bis Z1 vom "Gesamtwert" inA1 V1 [Edit] In A1 steht ja nach der Zerlegung schon das erste "Wort" - daher vor der Zerlegung die Spalte A nach zB Spalte V kopieren [/Edit] und damit die Ermittlung des Namens mit der Formel
Wenn es natürlich tatsächlich neben dem Namen mehr als 3 "Wörter" (zB 2 Vornamen) gibt, müsste das Konzept noch erweitert werden. Für die Zusammenfassung der Vornamen würde dann anstelle von
besser passen:
Ich hoffe, das war jetzt etwas verständlicher ...
Das ansgesprochene Problem, dass "Willy" auch ein Nachname sein könnte, lässt sich so natürlich nicht lösen - allerdings würde dann in der Spalte "Name" kein Wert mehr enthalten sein, was (speziell, nachdem nach Namen sortiert wurde) ja auffällt und dann manuell nachbearbeitet werden muss.
Genauso könnte auch mit
(unter der Annahme, dass der Name in der Spalte AA ermittelt worden wäre) in einer Hilfsspalte festgestellt werden, ob sich im Namen noch Leerstellen befinden (Ergebnis ist dann eine Zahl und nicht
Grüße
bastla
Nochmals zum zweiten Ansatz:
[Edit] Vor dem Zerlegen der Werte in der Spalte A durch "Text in Spalten" die gesamte Spalte A in die Spalte V kopieren! [/Edit]
Nachdem per "Text in Spalten" die Daten aus Spalte A aufgeteilt wurden (es sollte jetzt in den Spalten A bis zumindest D einzelne "Wörter" geben), wäre für jede der Ergebnisspalten nach 3 Kriterien zu suchen: Vorname, Titel und Beruf. In meinem Beispiel oben hatte ich angenommen, dass alle Vornamen in "Tabelle2!C1:C500" stünden - speziell die Adresse der letzten Zelle musst Du natürlich anpassen. Genauso habe ich für die Titel den Bereich "Tabelle2!D1:D20" und für die Berufe "Tabelle2!E1:E300" angenommen - auch hier sind Anpassungen erforderlich.
Unter der Annahme, dass die Spalte F keine Daten mehr enthält, könntest Du (anders als oben angedacht und damit hoffentlich übersichtlicher) dort damit beginnen, den Vornamen zu suchen - daher in F1 bis zumindest I1 die Formeln
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$C$1:$C$500;1;0));"";A1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$C$1:$C$500;1;0));"";B1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$C$1:$C$500;1;0));"";C1)
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!$C$1:$C$500;1;0));"";D1) Mit diesen Formeln wird versucht, die Wörter in A1, B1, C1 und D1 im "Vornamen"-Bereich "Tabelle2!$C$1:$C$500" (Absolutbezug ist wichtig) zu finden; wird der Name gefunden, wird die Originalschreibweise lt A1, B1, C1 oder D1 verwendet, ansonsten steht "" = "Nichts" in der Zelle. Falls (was zu erhoffen ist) nur genau eine der Zellen A1, B1, C1 und D1 einen Vornamen enthalten hat, befinden sich zB für den Fall, dass der Vorname in C1 steht, in den Zellen F1 und G1 der Wert "" (also "Nichts"), in H1 der Vorname und in I1 wieder "Nichts" - es ließe sich daher in zB der Zelle X1 die Formel
=F1&G1&H1&I1
Analog dazu könnten ab zB K1 die Spalten für den Titel folgen, und die Zusammenfassung
=K1&L1&M1&N1
Dann nochmals das Ganze für zB P1 bis S1 mit Zusammenfassung in Z1.
Was bliebe, wäre das "Subtrahieren" der Inhalte von X1 bis Z1 vom "Gesamtwert" in
=GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(V1;X1;"");Y1;"");Z1;""))
=F1&G1&H1&I1
=WECHSELN(GLÄTTEN(F1&" "&G1&" "&H1&" "&I1&" "&J1;" ";" ")
Das ansgesprochene Problem, dass "Willy" auch ein Nachname sein könnte, lässt sich so natürlich nicht lösen - allerdings würde dann in der Spalte "Name" kein Wert mehr enthalten sein, was (speziell, nachdem nach Namen sortiert wurde) ja auffällt und dann manuell nachbearbeitet werden muss.
Genauso könnte auch mit
=SUCHEN(" ";AA1)
#WERT!) und auch hier (nach Sortierung und damit Zusammenfassung aller entsprechenden Zeilen) manuell fortgesetzt werden.Grüße
bastla
Hallo Bastla,
zunächst vielen Dank für Deine Hilfe.
Der erste Teil die Aufteilung in einzelne Spalten, funktioniert sehr gut. Das Ergebnis der einzelnen Suche steht in X1, Y1 und Z1.
Nur die Namenserstellung durch die Subtraktion gelingt nicht.
Name = Inhalt V1 - Inhalt X1 - Inhalt Y1 - Z1
Kannst Du da bitte nochmals schauen
Vielen Dank
Excel Starter
zunächst vielen Dank für Deine Hilfe.
Der erste Teil die Aufteilung in einzelne Spalten, funktioniert sehr gut. Das Ergebnis der einzelnen Suche steht in X1, Y1 und Z1.
Nur die Namenserstellung durch die Subtraktion gelingt nicht.
Name = Inhalt V1 - Inhalt X1 - Inhalt Y1 - Z1
Kannst Du da bitte nochmals schauen
Vielen Dank
Excel Starter
Hallo Excel-Starter!
... gelingt inwiefern nicht?
Kurz zur Erklärung, was
macht (machen sollte) - dazu von innen nach außen lesen (und testen):
Der Inhalt von V1 wird um den Inhalt von X1 (durch Ersetzen durch "Nichts") reduziert - es sollte also noch der Name, der Titel und der Beruf verbleiben (sowie das Leerzeichen vor /nach dem entfernten Vornamen).
Wie vorhin, nur dass nun aus dem (Zwischen-)Ergebnis auch der Inhalt von Y1 entfernt wurde.
Jetzt müssten eigentlich alle "Worte" aus X1:Z1 durch "" ersetzt sein - es bleiben nur noch vor / nach dem Namen einige Leerzeichen, welche mit der Funktion "
Grüße
bastla
... gelingt inwiefern nicht?
Kurz zur Erklärung, was
=GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(V1;X1;"");Y1;"");Z1;""))
=WECHSELN(V1;X1;"")
=WECHSELN(WECHSELN(V1;X1;"");Y1;"")
=WECHSELN(WECHSELN(WECHSELN(V1;X1;"");Y1;"");Z1;"")
GLÄTTEN()" letztendlich eliminiert werden.Grüße
bastla
Hallo Bastla,
in Spalte V steht die Kopie des Textgemenges; beginnend mit V1
in Spalte X, Y und Z jeweils der Vorname, usw. Beginnend jeweils mit X1, Y1, Z1
AF1=GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(V1;X1;"");Y1;"");Z1;""))
Dann steht in der Spalte AF immer identisch der Inhalt von Spalte V, in gleicher Reihefolge und Inhalt, es wird nichts subtrahiert.
Grüße Excel starter
in Spalte V steht die Kopie des Textgemenges; beginnend mit V1
in Spalte X, Y und Z jeweils der Vorname, usw. Beginnend jeweils mit X1, Y1, Z1
AF1=GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(V1;X1;"");Y1;"");Z1;""))
Dann steht in der Spalte AF immer identisch der Inhalt von Spalte V, in gleicher Reihefolge und Inhalt, es wird nichts subtrahiert.
Grüße Excel starter
Hallo Excel-Starter!
Sorry - bei meinen Tests funktioniert das; die Werte in X1:Z1 stammen ja unmittelbar aus dem "Textgemenge" und dürften sich daher zB hinsichtlich Groß-/Kleinschreibung nicht vom Inhalt von V1 unterscheiden ...
Du könntest zB mit
(gibt die Zeichenanzahl an) zusätzlich überprüfen, dass sich keine Leerzeichen vor und nach dem Eintrag befinden; weiters kannst Du mit Kopieren der Zelle X1 und "Inhalte einfügen... / Werte" das exakte Ergebnis der Formel in einer anderen Zelle reproduzieren und dort (bzw in der Bearbeitungszeile) untersuchen ...
Grüße
bastla
Sorry - bei meinen Tests funktioniert das; die Werte in X1:Z1 stammen ja unmittelbar aus dem "Textgemenge" und dürften sich daher zB hinsichtlich Groß-/Kleinschreibung nicht vom Inhalt von V1 unterscheiden ...
Du könntest zB mit
=LÄNGE(X1)
Grüße
bastla
Hallo Bastla,
ein großes Danke an Dich, es funktioniert prächtig.
Ich hatte in meinem Eifer, um zu vermeiden, dass zwei Vornamen zusammengeschrieben werden (z.B. MariaJosef) die Sammelspalte wie folgt geschrieben.
=F1&" "&G1&" "&H1&" "&I1 die Leerzeichen führten zu der Nichtsubtraktion - sorry meine Schuld.
Jetzt werden zwar zwei Vornamen etc. zusammengeschrieben (soll mir jetzt auch egal sein) aber die Subtraktion funktioniert.
Nochmals vielen Dank (und bis zum nächsten Problem)
Grüße
Excel Starter
ein großes Danke an Dich, es funktioniert prächtig.
Ich hatte in meinem Eifer, um zu vermeiden, dass zwei Vornamen zusammengeschrieben werden (z.B. MariaJosef) die Sammelspalte wie folgt geschrieben.
=F1&" "&G1&" "&H1&" "&I1 die Leerzeichen führten zu der Nichtsubtraktion - sorry meine Schuld.
Jetzt werden zwar zwei Vornamen etc. zusammengeschrieben (soll mir jetzt auch egal sein) aber die Subtraktion funktioniert.
Nochmals vielen Dank (und bis zum nächsten Problem)
Grüße
Excel Starter
Hallo Excel-Starter!
Mein Vorschlag war ja deshalb auch
- um ganz auf Nummer sicher zu gehen, könnte sogar
verwendet werden.
Güße
bastla
Mein Vorschlag war ja deshalb auch
=WECHSELN(GLÄTTEN(F1&" "&G1&" "&H1&" "&I1&" "&J1;" ";" ")
=WECHSELN(WECHSELN(GLÄTTEN(F1&" "&G1&" "&H1&" "&I1&" "&J1;" ";" ");" ";" ")
Güße
bastla
