Zelle mit Kommas trennen und prüfen

Bild

Betrifft: Zelle mit Kommas trennen und prüfen
von: Bongartz
Geschrieben am: 19.09.2003 14:02:17

Hallo

ich habe eine Datei mit viele Adressen. Die sind teilweise mit Kommas Getrennt.

Beispiel:
Prakte GmbH, Tanne 2, 66459 Gubu, ILN-Nr. 4330952000008
Prakte Bau AG, Amwald 2, 66459 Gubu, ILN 4306236000002
Markt GmbH, Wankelstr. 5, 85046 Ingolstadt
Gastronomie GmbH , Filal-Nr.,BKZ, Kto.-Nr., Kontr.-Nr. Und Lieferanschrift
Gasttronomie GmbH, 51149 Köln, Hansestr. 67-71, ILN4335374000001
World GmbH Ingolstadt
Shopping GmbH, Weg 49, 50825 Köln ILN 4031097000006

Ich habe bereits herausgefunden wie ich die 13 Stellige ILN Nummer, die immer am Ende steht, Prüfen und in die nächste Spalte separieren kann.



Sub Testen()
Dim ivar As String, ivar1 As String
   
   ivar = Cells(2, 1)
   ivar1 = Right(ivar, 13)
   If IsNumeric(ivar1) Then
     Cells(2, 2).Value = ivar1
     With Selection
       .NumberFormat = "0000000000000"
       .HorizontalAlignment = xlLeft
     End With
   Else
     Cells(2, 2).Value = "Keine ILN"
   End If
   
End Sub


Code eingefügt mit Syntaxhighlighter 2.1





Mein Problem ist wie kann ich die PLZ und Ort finden und in Spalte 3 bis 4
schreiben?

Gruß
Bongartz
Bild


Betrifft: schon mal an DATEN-TEXT IN SPALTEN gedacht ot.
von: th.heinrich
Geschrieben am: 19.09.2003 14:09:55

.


Bild


Betrifft: AW: schon mal an DATEN-TEXT IN SPALTEN gedacht ot.
von: Bongartz
Geschrieben am: 19.09.2003 15:22:43

Hallo

es handelt sich um eine Exceldatei. die Daten stehen in einer Spalte.
Die ILN-Nr steht immer am Ende und ich kann sie deswegen auslesen.
Das Trennzeichen "," ist nicht immer existent und die PLZ und der Ort würden bei einem Excel Import oder über "Text in Spalten" nicht in der gleichen Spalte stehen.
Deswegen möchte ich ja die PLZ und den Ort über VBA Suchen.

Gruß
Bongartz


Bild


Betrifft: AW: schon mal an DATEN-TEXT IN SPALTEN gedacht ot.
von: th.heinrich
Geschrieben am: 19.09.2003 17:02:10

hallo Bongartz (vorname?)

Du kannst bei TEXT IN SPALTEN die TRENNZEICHEN kombinieren.

gruss thomas


Bild


Betrifft: AW: Zelle mit Kommas trennen und prüfen
von: Nayus
Geschrieben am: 19.09.2003 14:12:46

Hallo,
ich nehme an, dass die Datein in einer ASCII-Datei (".txt" oder ".csv") stehen.
Öffne diese Datei in EXCEL mit dem Dateifilter ("Textdateien").
Im folgenden Dialog gebe als Feldtrennzeichen das Komma an.
Beim Öffnen der Datei werden jetzt die Daten schön in Spalten geschrieben und das
Überprüfen ist viel einfacher.

Gruß,
Nayus


Bild


Betrifft: AW: Zelle mit Kommas trennen und prüfen
von: Bongartz
Geschrieben am: 19.09.2003 15:21:57

Hallo

es handelt sich um eine Exceldatei. die Daten stehen in einer Spalte.
Die ILN-Nr steht immer am Ende und ich kann sie deswegen auslesen.
Das Trennzeichen "," ist nicht immer existent und die PLZ und der Ort würden bei einem Excel Import oder über "Text in Spalten" nicht in der gleichen Spalte stehen.
Deswegen möchte ich ja die PLZ und den Ort über VBA Suchen.

Gruß
Bongartz


Bild


Betrifft: PLZ und Ort aus String filtern - Formellösung
von: Boris
Geschrieben am: 19.09.2003 18:34:01

Hi Bongartz,

eines vorneweg: Das wird niemals ne 100%ige Lösung geben - dafür ist der Stringaufbau zu unterschiedlich.

Wenn ich aber mal von Deinen Beispielen ausgehe, dass entweder
a) PLZ und Ort vorhanden sind UND vor der PLZ ein Leerzeichen und ein Komma steht
b) keine PLZ vorhanden ist

dann kann man schon einen Großteil mit Formel lösen.
In Spalte A ab A1 stehen deine Einträge.
In B1 folgende Array-Formel:
{=TEIL(A1;MIN(WENN((TEIL(A1;SPALTE($1:$1);1)=",")*ISTZAHL(TEIL(A1;SPALTE($1:$1)+2;1)*1);SPALTE($1:$1)+2));99)}
In C1 folgende Array-Formel:
{=LINKS(B1;MIN(WENN(ISTZAHL(FINDEN({",";" "};B1&",";7));FINDEN({",";" "};B1&",";7)))-1)}

B1 und C1 dann runterkopieren.

Da wird jetzt einigemale #WERT! auftauchen - und zwar immer dann, wenn KEINE PLZ vorhanden ist - aber auch oft genug PLZ und Ort = gewünschtes Ergebnis.
Probleme gibt´s zusätzlich bei Ortsnamen wie "53256 Bad Homburg". Das Ergebnis wird dann sein: "53256 Bad"

Das könnte man alles noch verfeinern und perfektionieren, aber vielleicht hilft´s dir als Ansatz.

Grüße Boris

Aber


Bild


Betrifft: AW: PLZ und Ort aus String filtern - Formellösung
von: Bongartz
Geschrieben am: 20.09.2003 18:24:49

Hallo Boris

super. Das werde ich gleich mal ausprobieren.
Bin immer wieder erstaunt was man mit den Excel Formeln alles anstellen kann.
Ich versuchs immer erst mit VBA, was meist nicht nötig ist.
Vielen dank für die Deine Hilfe.

Gruß
Bongartz


 Bild

Beiträge aus den Excel-Beispielen zum Thema " Zelle mit Kommas trennen und prüfen"