Microsoft Excel

Herbers Excel/VBA-Archiv

Zellinhalt vor einem bestmmten Zeichen

Betrifft: Zellinhalt vor einem bestmmten Zeichen von: Jürgen Schmiedeshoff
Geschrieben am: 25.03.2020 11:41:27

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

Betrifft: AW: Zellinhalt vor einem bestmmten Zeichen
von: SF
Geschrieben am: 25.03.2020 11:47:10

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

Betrifft: AW: Zellinhalt vor einem bestmmten Zeichen
von: Jürgen Schmiedeshoff
Geschrieben am: 25.03.2020 13:19:27

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

Betrifft: AW:so ganz einfach dürfte das nicht sein, denn ...
von: neopa C
Geschrieben am: 25.03.2020 11:59:37

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
.. , - ...


Betrifft: AW: AW:so ganz einfach dürfte das nicht sein, denn ...
von: Jürgen Schmiedeshoff
Geschrieben am: 25.03.2020 12:21:13

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

Betrifft: AW: AW:so ganz einfach dürfte das nicht sein, denn ...
von: Günther
Geschrieben am: 25.03.2020 12:25:01

Jürgen, das hat sich überschnitten.
Und in 2003 kannst du PQ vergessen.

Gruß
Günther

Betrifft: AW: dann reicht auch eine Formel ohne {} ...
von: neopa C
Geschrieben am: 25.03.2020 12:41:31

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
.. , - ...

Betrifft: da gibt's noch anderes / besseres
von: WF
Geschrieben am: 25.03.2020 13:00:41

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

Betrifft: das war Blödsinn
von: WF
Geschrieben am: 25.03.2020 13:18:57

.

Betrifft: AW: Zellinhalt vor einem bestmmten Zeichen
von: Günther
Geschrieben am: 25.03.2020 12:20:54

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

Betrifft: per VBA
von: UweD
Geschrieben am: 25.03.2020 13:17:41

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

Betrifft: Mit VBA-basierten UDFs in einer ZellFml ...
von: Luc:?
Geschrieben am: 25.03.2020 14:38:47

…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 …


Betrifft: AW: Zellinhalt vor einem bestmmten Zeichen
von: Daniel
Geschrieben am: 25.03.2020 16:31:13

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

Betrifft: Netter Versuch mit immerhin 3 von 4 Richtigen, ...
von: Luc:?
Geschrieben am: 25.03.2020 18:03:58

…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 :-?

Betrifft: AW: Netter Versuch mit immerhin 3 von 4 Richtigen, ...
von: Daniel
Geschrieben am: 25.03.2020 19:42:49

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)

Betrifft: Das wäre besser, ...
von: Luc:?
Geschrieben am: 27.03.2020 15:07:37

…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 :-?

Betrifft: Sollten wir dann nicht auch berücksichtigen
von: Daniel
Geschrieben am: 27.03.2020 18:40:12

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.

Betrifft: Daran hatte ich sogar gedacht, ...
von: Luc:?
Geschrieben am: 27.03.2020 19:39:30

…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 :-?

Beiträge aus dem Excel-Forum zum Thema "Zellinhalt vor einem bestmmten Zeichen"