Office: Inhalt von Zellen umwandeln!

Hallo zusammen,

also ich hab folgendes Problem.
Ich habe eine Spalte mit folgendem Zelleninhalt:

Platz 2, Parkstraße 27, 82061 Neuried
Fußball-Abt., Georgenstr. 35, 82152 Planegg
Platz 2, Parkstraße 27, 82061 Neuried
MTV 1879, Werdenfelsstr. 70, 81377 München


ich benötige aber folgenden Inhalt (in einer neuen Spalte)

Parkstr.+27+82061+Neuried
Georgenstr.+35+82152+Planegg
Parkstr.+27+82061+Neuried
Werdenfelsstr.+70+81377+Muenchen

hierbei sind folgende Punkt wichtig!

1. der vordere Teil bis zum ersen Komma muss verschwinden inkl. des Leerzeichens!
2. straße muss durch str. ersetzt werden
3. ß ü ö ä muss durch ss ue oe ae ersetzt werden
4. alle Leerzeichen müssen verschwinden und die einzelnen Adresssegmente durch + verbunden sein

Ich hoffe jemand von euch hätte da eine Lösung für mich :)

Danke schon mal
LG
Ernst

 

Hallo Ernst,

arbeite doch mit Strg+H

Gruß Hajo

 

Hallo Hajo,

ich möchte das nicht immer "manuell" machen müssen ... es sind ca. 1000 Datensätze die sich immer mal wieder erneuern ;)

Gruß
Ernst

 

Hallo Ernst,

die Aktionen kannst Du beim ersten mal mit dem Mkarorecrder aufzeichenen und beim zweiten mal nur das Makro starten.

Gruß Hajo

 

Die folgende Routine macht das:

Code:

Private Sub CommandButton1_Click()

    Dim s1 As Worksheet
    Dim y1 As Long
    Dim x1 As Long
    Dim tst1 As String
    Dim v As String
    Dim n As Long

    Set s1 = ActiveSheet

    y1 = 1: x1 = 1
    s1.Activate: ActiveSheet.Cells(y1, x1).Activate
    tst1 = Trim$(s1.Cells(y1, x1).Value)
    While (tst1 <> "")

        v = Trim$(s1.Cells(y1, x1).Value)

        n = InStr(1, v, ",", vbTextCompare)
        v = Right$(v, Len(v) - n - 1)

        v = Replace(v, "Ä", "Ae")
        v = Replace(v, "Ö", "Oe")
        v = Replace(v, "Ü", "Ue")
        v = Replace(v, "ä", "ae")
        v = Replace(v, "ö", "oe")
        v = Replace(v, "ü", "ue")
        v = Replace(v, "ß", "ss")

        v = Replace(v, "strasse", "str.")

        v = Replace(v, ",", "")

        v = Replace(v, " ", "+")

        s1.Cells(y1, x1 + 1).Value = v

        y1 = y1 + 1
        s1.Activate: ActiveSheet.Cells(y1, x1).Activate
        tst1 = Trim$(s1.Cells(y1, x1).Value)
    Wend

End Sub

Daten ab A1 abwärts, Ergebnisse in Spalte rechts daneben. Wenn A1 nicht paßt, dann in

Code:

y1 = 1: x1 = 1

Startzelle anpassen.

Diese Routine arbeitet die Spalte Zelle für Zelle durch. Dadurch ist sie recht flexibel und erweiterbar, z.B. mit Reaktionen auf bestimmte Inhalte, um z.B. Zellen einzufärben oder was auch immer.

Gruß, Michael

 

Super :)

des funktioniert klasse :)

kann ich eig. auch irgend wie dafür sorgen dass beim Öffnen der Datei diese Umwandlung sofort automatisch passiert und wenn sich die Ausgangszelle ändert, auch die Zielzelle?

Gruß und Danke
Ernst

 

Yup, das geht.

Beim Öffnen der Arbeitsmappe wird ein Ereignis ausgelöst, in das man sich einklinken kann. Ruft man dort die Routine auf, hat man den aktuellen Stand direkt nach dem Öffnen der Mappe.

Auch beim Ändern eines Zellinhalts wird ein Ereignis ausgelöst, in das man sich einklinken kann. Zusammen mit einer Bereichs-Überprüfung (wo hat sich was geändert?) kann man so die Liste auf aktuellem Stand halten.

Für diese Möglichkeiten ist ein kleiner Umbau des VBA-Codes notwendig:

a) im VBA-Editor (Alt-F11) muß zunächst ein neues Modul (Menü "Einfügen - Modul") erstellt werden. Der Rumpf der bisherigen Button-Routine wird dorthin in eine global verfügbare (Stichwort "public") Routine verlagert. Ich hab die Routine dann auch noch in "Liste abarbeiten" und "Zeile bearbeiten" aufgeteilt:

Modul1:

Code:

Option Explicit

Public Const Liste_Anfang = "a1"

Public Sub Zeile_konvertieren(rng As Range)

        Dim s1 As Worksheet
        Dim y1 As Long
        Dim x1 As Long
        Dim v As String
        Dim n As Long

        y1 = rng.Row
        x1 = rng.Column

        Set s1 = ActiveSheet
        s1.Activate: ActiveSheet.Cells(y1, x1).Activate

        v = Trim$(s1.Cells(y1, x1).Value)

        n = InStr(1, v, ",", vbTextCompare)
        v = Right$(v, Len(v) - n - 1)

        v = Replace(v, "Ä", "Ae")
        v = Replace(v, "Ö", "Oe")
        v = Replace(v, "Ü", "Ue")
        v = Replace(v, "ä", "ae")
        v = Replace(v, "ö", "oe")
        v = Replace(v, "ü", "ue")
        v = Replace(v, "ß", "ss")

        v = Replace(v, "strasse", "str.")

        v = Replace(v, ",", "")

        v = Replace(v, " ", "+")

        s1.Cells(y1, x1 + 1).Value = v

End Sub

Public Sub Liste_konvertieren()

    Dim s1 As Worksheet
    Dim y1 As Long
    Dim x1 As Long
    Dim tst1 As String
    Dim NewTarget As Range

    Set s1 = ActiveSheet
    Set NewTarget = ActiveCell

    y1 = s1.Range(Liste_Anfang).Row: x1 = s1.Range(Liste_Anfang).Column
    's1.Activate: ActiveSheet.Cells(y1, x1).Activate
    tst1 = Trim$(s1.Cells(y1, x1).Value)
    While (tst1 <> "")

        Zeile_konvertieren s1.Cells(y1, x1)

        y1 = y1 + 1: x1 = s1.Range(Liste_Anfang).Column
        's1.Activate: ActiveSheet.Cells(y1, x1).Activate
        tst1 = Trim$(s1.Cells(y1, x1).Value)
    Wend

    NewTarget.Activate

End Sub

Der Code der Button-Routine ändert sich daraufhin in eine einzige Zeile, nämlich den Aufruf der Konverter-Routine.

b) Außerdem brauchst Du noch die Routine, die auf das Ändern der Zellen reagiert. Die gehört auch in "Tabelle1" neben die Button-Routine.

Tabelle1:

Code:

Option Explicit

Private Sub CommandButton1_Click()

    Liste_konvertieren

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim NewTarget As Range
    Dim s1 As Worksheet
    Dim Liste_Bereich As Range

    Set s1 = ActiveSheet
    Set NewTarget = ActiveCell
    Set Liste_Bereich = s1.Range(s1.Range(Liste_Anfang), s1.Range(Liste_Anfang).End(xlDown))

    If (Not Application.Intersect(Liste_Bereich, Target) Is Nothing) Then
        Zeile_konvertieren Target
        NewTarget.Activate
    End If

End Sub

c) Und zum Schluß noch das Konvertieren beim Öffnen: Hierfür ist das globale Modul "DieseArbeitsmappe" zuständig. Der Aufruf sieht genau so aus, wie beim Button.

DieseArbeitsmappe:

Code:

Option Explicit

Private Sub Workbook_Open()

    Liste_konvertieren

End Sub

Das sollte es dann gewesen sein...

Gruß, Michael

 

Also ich habe leider noch kaum Erfahrungen mit VBA

Ich weiß des sind absolute Anfängerfragen!

Wie binde ich das denn genau ein?

Sorry für meine unwissenheit

 

Dafür braucht sich niemand entschuldigen. ;-) Lernresistent und merkbefreit wären da schon eher problematisch. :-)

Ok, in meinem letzen Posting waren 3 Code-Blöcke für "Modul1", "Tabelle1" und "DieseArbeitsmappe". Wenn du Deine Excel-Tabelle geöffnet hast und auf "Tabelle1" stehst, kommst Du mit Alt-F11 in den VBA-Editor und normalerweise auch in das Modul, daß zu "Tabelle1" gehört.

Jetzt wäre der richtige Augenblick, mit Strg-R (oder über das Menü "Ansicht - Projekt-Explorer") die Übersicht über die einzelnen Module einzublenden. Sollte jetzt plötzlich irgendein Fenster zu gehen, einfach nochmal, denn dann war es schon angezeigt.

Im Projekt-Explorer findest Du u.a. wohl eine Zeile "VBAProject (Mappe1.xls)", was Deiner gerade geöffneten Excel-Mappe entspricht. Sollte dort ein [-] davor stehen, klicke darauf und öffne den Baum genau wie im Datei-Explorer.

Wähle jetzt aus dem Menü "Einfügen - Modul" und es sollte im Explorer in dem Baum ein "Modul1" erscheinen und im großen Fenster ein mehr oder weniger weißes Blatt. Kopiere dort den ganzen Kladderadatsch zu "Modul1" aus meinem Posting hinein.

Wähle jetzt "Tabelle1" per Doppelklick aus dem Baum und kopiere den entsprechenden Part aus meinem Posting dort hinein.

Und zum Schluß wähle noch "DieseArbeitsmappe" aus dem Baum und kopiere wieder den entsprechenden Part aus meinem Posting dort hinein.

Wenn Du jetzt mit Alt-F11 wieder zu Excel zurück wechselst und die Mappe mit Speichern schließt, sollte es nach dem nächsten Öffnen direkt die Liste durchackern und auch auf Änderungen direkt reagieren.

Gruß, Michael

 

Suuuuuper :)

also Lernresistent bin ich wohl nicht :)
es funktioniert ...

Vielen Dank