Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 21:51:33
Idee für Grafik
26.04.2024 21:29:07
PQ zufällige Reihenfolge
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1744to1748
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
1744to1748
1744to1748
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zellinhalt vor einem bestmmten Zeichen

Zellinhalt vor einem bestmmten Zeichen
25.03.2020 11:41:27
Jürgen
Hallo,
ich möchte eine Kundendatei bereinigen in der auf Grund eines EDV-Fehlers bei manchen Datensätzen die Hausnummer doppelt aufgenommen wurde.
Beispiel:
Bertha-von-Suttner-Str. 30 30
Gewünschtes Ergebnis:
Bertha-von-Suttner-Str. 30
Mit =LINKS([Zelle];FINDEN(" ";[Zelle])-1) komme ich nicht weiter, da ein Leerzeichen mehrfach in den Straßenanschriften auftauchen kann und in diesem fall als Ergebnis
Bertha-von-Suttner-Str.
liefert. Ich benötige alsoden Zellinhalt links vom zuletzt in der Zelle auftauchenden Leerzeichen.
Vermutlich einfach - wenn man's weiß...
Ich bedanke mich herzlich für eure Hilfe!
(Und die habe ich schon oft in diesem Forum gefunden!)
Jürgen

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellinhalt vor einem bestmmten Zeichen
25.03.2020 11:47:10
SF
Hola,
z.B.: 
=LINKS(A1;MAX((TEIL(A1;SPALTE(1:1);1)=" ")*SPALTE(1:1))-1)

Die Formel mit Strg-Shift Enter abschließen.
Gruß,
steve1da
AW: Zellinhalt vor einem bestmmten Zeichen
25.03.2020 13:19:27
Jürgen
Hallo, steve1da,
danke für deine Hilfe.
Das hat ganz wunderbar funktioniert!
Schöne und gesunde Zeit wünsche ich dir und allen anderen in diesem forum1
Jürgen
AW:so ganz einfach dürfte das nicht sein, denn ...
25.03.2020 11:59:37
neopa
Hallo Jürgen,
.. wie Du schreibst, dass nur bei einigen Datensätzen auftaucht. Denn eine solche Formel soll doch bestimmt auch abprüfen, ob die Nr wirklich doppelt vorhanden ist, oder? Wenn ja, dann wäre noch die Fragen zu klären, ob die Nummer immer als letztes steht und außerdem welche Excelversion Du genau im Einsatz hast.
Gruß Werner
.. , - ...
Anzeige
AW: AW:so ganz einfach dürfte das nicht sein, denn ...
25.03.2020 12:21:13
Jürgen
Hallo,
mir würde es tatsächlich reichen, das Ergebnis so zu bekommen wie beschrieben.
Die Kundendatei hatte ich dahingehend gefiltert, dass in der Exceldatei (Excel 2003 übrigens) nur Datensätze auftauchen, an deren Ende eine Ziffer gefolgt von einem Leerzeichen und einer weiteren Ziffer steht.
Natürlich gibt es Ausnahmen wie z.B. Adressen in Berlin mit "Straße 43 18", aber die sehe ich ich dann unter den ca. 500 Datensätzen problemlos und kann die aus der Verarbeitung rausnehmen. Datensätze wie "Würzburger Str. 18a 18a" muss ich in einem weiteren Schritt erst mal aus der Kundendatei extrahieren, da die o.g. Bedingung dabei ja nicht greift.
Den Lösungsvorschlag aus der anderen Antwort probiere ich gleich mal aus.
Liebe Grüße aus Münster
Jürgen
Anzeige
AW: AW:so ganz einfach dürfte das nicht sein, denn ...
25.03.2020 12:25:01
Günther
Jürgen, das hat sich überschnitten.
Und in 2003 kannst du PQ vergessen.
Gruß
Günther
AW: dann reicht auch eine Formel ohne {} ...
25.03.2020 12:41:31
neopa
Hallo Jürgen,
... für Adresse in A1 z.B. so:
=LINKS(A1;VERWEIS(9;1/(TEIL(A1;ZEILE(A$1:A$99);1)=" ");ZEILE(A:A)))
Formel nach unten kopierbar.
Gruß Werner
.. , - ...
da gibt's noch anderes / besseres
25.03.2020 13:00:41
WF
Hi,
{=LINKS(A1;VERGLEICH(" ";TEIL(A1;SPALTE(1:1);1);-1)-1)}
bzw. ohne {} mit INDEX-Verlängerung:
=LINKS(A1;VERGLEICH(" ";INDEX(TEIL(A1;SPALTE(1:1);1););-1)-1)
WF
das war Blödsinn
25.03.2020 13:18:57
WF
.
AW: Zellinhalt vor einem bestmmten Zeichen
25.03.2020 12:20:54
Günther
Moin,
das sollte mit Power Query (kostenloses MS-Add-In) machbar sein. Probleme sehe ich aber bei Zusätzen wie beispielsweise Bahnhofstraße 29 B oder Bahnhofstraße 29 B 29 B
Eine Muster-xlsx mit 10-20 Datensätzen mit und ohne Doppelungen wäre hilfreich.
Gruß
Günther
Anzeige
per VBA
25.03.2020 13:17:41
UweD
Hallo
- Splitten bei Leerzeichen
- Letztes und Vorletztes Datenfeld gleich, dann Letztes löschen
Sub Hausnummer()
    Dim Arr, i As Integer, MMAx As Integer
    Dim SP As Integer, LR As Integer
    
    SP = 1 'Daten in Spalte A 
    LR = Cells(Rows.Count, 1).End(xlUp).Row 'letzte Zeile der Spalte 
    
    
    For i = 1 To LR
        Arr = Split(Cells(i, SP), " ")
        
        MMAx = Ubound(Arr) 'Höchtes Datenfeld 
        If Arr(MMAx) = Arr(MMAx - 1) Then 'doppelt am Ende 
            Arr(MMAx) = ""
        End If
        
        Cells(i, SP + 1) = Join(Arr)
    
    Next
    
End Sub

LG UweD
Anzeige
Mit VBA-basierten UDFs in einer ZellFml ...
25.03.2020 14:38:47
Luc:?
…kann man das so machen, Jürgen,
dass es für alle 4 im bisherigen Verlauf des Threads genannten TextBspp funktioniert:
Aus {"Bahnhofstraße 29 B 29 B";"Bertha-von-Suttner-Str. 30 30";"Würzburger Str. 18a 18a";"Straße 43 18"} wird so {"Bahnhofstraße 29 B";"Bertha-von-Suttner-Str. 30";"Würzburger Str. 18a";"Straße 43 18"} (hier notiert in Matrixkonstantenform).
Die singulare MatrixFml (Mx) besteht aus 8 Fktt (ƒ), davon 4 UDFs (verlinkt):
Mx:= WENN(CountOn(A1;INDEX(VSplit(MaskOn(A1;"num"));1))>1;GLÄTTEN(LINKS(A1;VLike(A1;"*"&INDEX(VSplit(MaskOn(A1;"num"));1)&"*";-1)-1));A1)
ƒ1:= MaskOn(A1;"num")
ƒ2:= VSplit(ƒ1)
ƒ3:= INDEX(ƒ2;1)
ƒ4:= VLike(A1;"*"&ƒ3&"*";-1)
ƒ5:= LINKS(A1;ƒ4-1)
ƒ6:= GLÄTTEN(ƒ5)
ƒ7:= CountOn(A1;ƒ3)
ƒ8:= WENN(ƒ7>1;ƒ6;A1)
Gruß, Luc :-?
„Der beste Beweis für intelligentes Leben im Universum ist, dass noch niemand versucht hat, Kontakt mit uns aufzunehmen.“ H.Lesch, 2018, Sonneberg
Deshalb Intelligenz steigern mit …
Anzeige
AW: Zellinhalt vor einem bestmmten Zeichen
25.03.2020 16:31:13
Daniel
Hi
Beispielweise so,
Die Formeln schreibe ich für dir Annahme, dass die Adresse in A1 steht:

1. Extrahiere die letzte Hausnummer mit dieser Formel:
B1: =Glätten(Rechts(Wechseln(A1," ";Wiederholen(" ";100);100))
2. Ersetzte dann die doppelte Hausnummer durch die einfache Hausnummer. Dort, wo das Problem nicht besteht, bleibt der Wert unverändert:
C1: =Wechseln(A1;" "&B1&" "&B1;" "&B1)
Gruß Daniel
Netter Versuch mit immerhin 3 von 4 Richtigen, ...
25.03.2020 18:03:58
4
…Daniel,
wenn man erstmal die 2 Fehler in deiner 1.Fml beseitigt hat. Für das Bahnhofstraßen-Bsp klappt es nicht wg der Orientierung auf die Zwischenräume, was dabei nur das B erwischt. Eine Suche nach der letzten Zahl wäre deshalb idR universeller (nur reine BuchstabenNummerierungen wären dann unpassend). Dabei könnte durchaus auch eine kürzere, elegantere und weniger aufwendige Fml als meine herauskommen.
Gruß, Luc :-?
Anzeige
AW: Netter Versuch mit immerhin 3 von 4 Richtigen, ...
25.03.2020 19:42:49
4
Aufgabenstellung des Fragestellers:
"Ich benötige alsoden Zellinhalt links vom zuletzt in der Zelle auftauchenden Leerzeichen."
demnach kommen Angaben wie "30 b" wohl nicht vor.
sonst hätte er auch für die Lösung von SF kein solches Feedback geben dürfen.
natürlich kann man jede Lösung schlecht reden, wenn man ganz spezielle Sonderfälle bestimmt.
der Vorteil meiner Lösung wäre, dass sie auch dann angewendet werden kann, wenn korrekte Daten (Hausnummer kommt nur 1x vor) und zu korrigierende Daten (Hausnummer kommt 2x vor) gemischt vorliegen.
dein letzter Gedankenansatz ließe sich dann wohl so forumlieren:
=LINKS(A1;AGGREGAT(14;6;ZEILE($A$1:$A$100)/(TEIL(A1;ZEILE($A$1:$A$100);1)=" ")/ISTZAHL(-TEIL(A1; ZEILE($A$1:$A$100)+1;1));1)-1)
Anzeige
Das wäre besser, ...
27.03.2020 15:07:37
Luc:?
…Daniel,
denn das berücksichtigt die möglichen unterschiedlichen Schreibungen, die bei sehr vielen Daten garantiert auftreten. Jetzt hakt's nur noch bei Straße 43 18, was zuvor funktionierte (allerdings nicht in der Form Straße 43 18 43 18, falls das überhaupt vorkommt). Allerdings sind mir solche Adressen ohnehin unbekannt (ganz ohne Verbindungszeichen zwischen den Zahlen, auch aus Berlin) und im Datenmaterial evtl anders entstanden, so dass der Korrektor dann tatsächlich manuell arbeiten müsste.
Dass der Fragesteller nicht an alle regulär (ggf auch häufig) auftretenden Varianten gedacht hat, hindert uns ja nicht daran, diese ebenfalls zu berücksichtigen bzw darauf hinzuweisen. Es müssen ja nicht alle sein, sondern wenigstens die häufigsten u/o plausibelsten.
Luc :-?
Anzeige
Sollten wir dann nicht auch berücksichtigen
27.03.2020 18:40:12
Daniel
Dass die Hausnummer mit einem Buchstaben beginnt?
Oder gar keine Hausnummer vorhanden ist?
Warum kann man nicht einfach davon ausgehen, dass der Fragesteller sich seine Daten vorher angeschaut hat?
Und wenn nicht, wird er es schnell lernen, seine Fragen präziser zu stellen.
Daran hatte ich sogar gedacht, ...
27.03.2020 19:39:30
Luc:?
…Daniel,
aber darauf verzichtet, weil das sicher sehr selten ist und wir ja auch nicht wissen, wie sich der EDV-Fehler darauf ausgewirkt hätte(/hat). Dennoch war aber zumindest das (nicht von mir stammende) Bsp Bahnhofstraße berechtigt.
Luc :-?

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige