Postleitzahl und Stadt trennen

[liselotte]

Excel 360
Hi Leutz,
ich habe ein Problem, oft bekomme ich Exceltabellen in denen die PLZ und die Stadt in einer Zelle sind.
Das mag das Versandprogramm gar nicht.
Wie kann ich in Excel diese beiden Felder wieder trennen?

10117 Berlin

10117

Berlin

Noch eine kleine Frage:
wie bekomme ich "Textkennzeichner" wie "Punkt" oder "Apostroph" wieder aus der Zelle?
Für Eure Hilfe wäre ich dankbar
Besinnliche Tage
MfG

 

[liselotte]

Das Problem mit der Postleitzahl wäre gelöst
Bleibt nur noch das Problem mit den "Sinnlosen Zeichen"
MfG

 

[Gelöschtes Mitglied 746345]

Funktioniert in Exel die normale Suchen und Ersetzen-Funktion?

 

[derwerner]

Das Problem mit der Postleitzahl wäre gelöst

Schön für Dich, dass das Problem gelöst ist - Noch schöner wäre es, wenn Du uns an dieser Lösung teilhaben lassen würdest....

 

[Dagobert68]

Moin alle zusammen

Hier mal ein allgemein gültiges Beispiel, wie man Zeichenketten bei einem bestimmten Zeichen trennt - in diesem Fall soll eine E-Mail-Adresse in den Teil links vom @-Zeichen (Username) und den Teil rechts vom @-Zeichen (Domain) zerlegt werden:

E-Mail-Adresse: test@psd-tutorials.de steht in Zelle A1
Ergebnis: test soll in Zelle B1 stehen und psd-tutorials.de in Zelle C1

Formel für Zelle B1: LINKS(A1;(SUCHEN("@";A1)-1))
Formel für Zelle C1: RECHTS(A1;(LÄNGE(A1)-SUCHEN("@";A1)))

Erläuterung Formel B1:
LINKS trennt alles ab einer bestimmten Zeichenlänge links davon ab. Dazu wird mit SUCHEN("@";A1) ermittelt, wie viele Zeichen es inklusive @ gibt von links bis zum @ gezählt. Davon wird 1 abgezogen (das @-Zeichen soll nicht mitgezählt werden). Das Ergebnis dieser Zeichenanzahl wird dann in der Formel LINKS verwendet als Angabe, bis zu welchem Zeichen links des Eintrags abgetrennt werden soll.

Erläuterung Formel C1:
Hier wird nach dem gleichen Prinzip alles rechts vom @-Zeichen ermittelt. Dazu wird wieder mit SUCHEN("@";A1) ermittelt, wie viele Zeichen links vom @ stehen (inkl. @-Zeichen). Dieser Wert wird dann von der Gesamtzeichenlänge (LÄNGE(A1) von A1 abgezogen, so dass als Ergebnis die Zeichenlänge rechts vom @ übrig bleibt, die dann wiederum für die RECHTS-Formel verwendet wird.

Nach dem gleichen Prinzip könnte man so auch Postleitzahl und Ort trennen, in dem Fall dann halt statt des @ ein Leerzeichen als Trenner in der Formel angeben (also zum Suchen zum Beispiel so: SUCHEN(" "; A1), wobei man für diesen speziellen Fall auch die eingebaute Excel-Funktion "Text in Spalten" (im Ribbon "Daten" zu finden) verwenden könnte.

"Text in Spalten" ist jedoch nicht ganz so flexibel wie die Formeln, da "Text in Spalten" grundsätzlich den gesamten Text bei allen vorhandenen Leerzeichen in entsprechend viele Spalten aufteilen würde. Hätte man also einen Ort, der ein Leerzeichen enthält, bekäme man drei Spalten - in etwa so zum Beispiel: "08150 Bergisch Gladbach" ergäbe dann eine Spalte mit 08150, eine mit Bergisch und eine mit Gladbach, was sicher nicht gewünscht wäre an der Stelle.

Fröhliches Verformeln und einen guten Start in den Tag

Liebe Grüße,
Tina

 

[owieortho]

Habe bisweilen bei Datenbankausgaben falsch codierte Umlaute und Ligaturen. Jedesmal. Suchen und ersetzen ist dann immer ziemlich langwierig, habe daher für einen Fall den Vorgang als Makro aufgezeichnet und für die weiteren Fälle im VBA-Editor die Iterationen ergänzt. Das ginge sicherlich mit einer Programmierung mittels eines arrays und entsprechender Schleife noch eleganter, aber so ist es ausreichend. Jetzt läuft jedesmal bei der Ausgabe anschließend das Makro drüber und gut ist's.
Für das Zerlegen von beispielsweise PLZ und Ort kann man ohne Funktionen und Formeln "Text in Spalten" verwenden. Findet sich im Register Daten. Der folgende dreistufige Dialog fragt erst nach fester Breite.
Hier würde man beim genannten Beispiel im zweiten Schritt die Umbruchlinie auf die fünfte und sechste Position setzen deutsche PLZ vorausgesetzt). Man erhält PLZ und Ort in separaten Spalten aber mit einer leeren Spalte zwischen den beiden.
Sinnvoller ist im ersten Schritt "getrennt" nehmen. Dann gibt man im zweiten Schritt das Leerzeichen als Trenner an (den vorausgewählten Tabstopp kann man, muss man aber nicht, löschen). Das gibt dann wiederum zwei Spalten mit PLZ und Ort und diesmal ohne leere Spalte. Mit dieser Variante können auch in einem Schritt kombinierte Trennzeichen entfernt werden, bspw., wenn Nachname, Vorname in einer Zelle stehen. Dann gibt man eben das Leerzeichen und das Komma als Trennzeichen an.
Viel Erfolg und entspannte Festtage
O.

 

[liselotte]

Funktioniert in Exel die normale Suchen und Ersetzen-Funktion?

Ja doch aber mit der Axt, auch in anderen Spalten zB. Str. ist dann ohne Punkt

Wow Danke Euch

meine neuerliches Prob ist jetzt dieses dass, das Progrie gar nicht mit Zahlen klarkommt die 01234 heißen da wird die vorangestellte "Null" ignoriert (aber meckern)
Dieses Problem wird mich wohl noch eine Weile beschäftigen.
Bis bald ich werde berichten und ein wenig rumprobieren
Danke
MfG

 

[owieortho]

...dass, das Progrie gar nicht mit Zahlen klarkommt die 01234 heißen da wird die vorangestellte "Null" ignoriert..

Das bekommst Du mit einer benutzerdefinierten Formatierung in den Griff. Markiere dazu die betroffenen (PLZ-)Zellen und rufe dann mit STRG+1 die Zellformatierung auf. Dort, im Reiter Zahlen, wählst Du im Feld "Kategorie" "Benutzerdefiniert" aus und trägst dann im Feld "Typ" 00000 ein. Damit werden alle Zahlen mindestens fünfstellig angezeigt und bei weniger als fünf Stellen wird eine oder werden mehrere führende Nullen vorangestellt. Mit OK bestätigen and Bob's your uncle...
Gruß in die alte Heimat
O.

 

[lustig]

Ich glaube, die benutzerdefinierte Formatierung sollte so lauten: 0####

 

[wex_stallion]

Nimm halt die Funktion, die u.a. für sowas gedacht ist.

 

[liselotte]

@wex_stallion das verstehe ich leider nicht, denn dann würden ja auch Doppevor- oder Hinternamen auch aufgeteilt werden bei Straße und Hausnummer wäre es mir recht.
Ich probiere es trotzdem mal

Der Große Bockmist bei der Formatierung als "00000" ist dass, das Programm es falsch einließt (haut mir immer die erste Null weg), also muss ich mit Textkennzeichner arbeiten

MfG

Programm ist das "DPD" Paketprogramm um Labels zu erstellen

 

[wex_stallion]

Musst das ja nicht global aufs ganze Dokument draufwerfen, das funktioniert auch spaltenweise … ggf. muss man halt mehrfach ran. Erstmal importieren, dann angucken und entsprechend die Tools wählen.

Zum DPD-Labelprogramm: Das kenne ich. Wenn weniger als 10.000 Adressen ist die Handerfassung im DPD-Programm selber die zeitsparendere Variante …

 

[liselotte]

Wenn weniger als 10.000 Adressen ist die Handerfassung im DPD-Programm selber die zeitsparendere Variante …

Da krieg ick ja schiss
maximal 20 gebe ich per Hand ein, ab da verlange (oder mache dann doch selber) ich eine "Excel-Datei" und mappe das in die Datenbank (mit halt diesen Einschränkungen) jetzt habe ich ja so einigermaßen den Workflow den ich noch ausbauen werde
Zumindest das Trennen von PLZ u. Stadt funktioniert und das zusammenlegen von Vor- u. Nachnamen

Danke Euch allen der Hilfe und wünsche ein Frohes Fest
MfG

PS ich hoffe ich bleibe dieses Jahr damit noch verschont (Weihnachtspakete und Post sollte vorbei sein)
Nächstes Jahr kommen bestimmt wieder die Jahresberichte aber die gehen auch meistens per Post

 

[owieortho]

...dann würden ja auch Doppevor- oder Hinternamen auch aufgeteilt werden...

Namen zu trennen, kann mit dem "Text in Spalten"-Werkzeug eine Crux sein. Exzessive Vornamen, von-, zu-, genannt-Zusätze, mehrere Nachnamen (bspw. bei spanischen Nachnamen) etc. pp. und schon hat man alles so schön aufgeteilt, dass es hintenan mühselig wird, es stimmig zusammenzufügen. Für bis zu drei Vornamen (ja, muss man abzählen und es eintragen) habe ich dass schon mal in Excel realisiert:
In Spalte A stehen Vorname Nachname, in Spalte B gibst Du an, wieviele der Namen Vornamen sind, C ist leer, D enthält folgende Formel:
=WENN($B3=1;LINKS(A3;FINDEN(" ";A3)-1);WENN($B3=2;LINKS(A3;FINDEN(" ";A3;FINDEN(" ";A3)+1)-1);WENN($B3=3;LINKS(A3;FINDEN(" ";A3;FINDEN(" ";A3;FINDEN(" ";A3)+1)+1)-1))))
E folgende:
=RECHTS(A3;LÄNGE(A3)-LÄNGE(D3)-1)
Tob Dich gerne aus und schachtele weitere Verzweigungen für vier und mehr Vornamen (für den vollen Guttenberg siehe die Wikipedia) rein...

Der Große Bockmist bei der Formatierung als "00000" ist dass, das Programm es falsch einließt (haut mir immer die erste Null weg), also muss ich mit Textkennzeichner arbeiten...
Programm ist das "DPD" Paketprogramm um Labels zu erstellen...

Sowohl bei einer Zahlenformatierung als Text, als auch als als 00000 bzw. 0####, wird eine PLZ mit führender Null beim kopieren vollständig mitübertragen. Das scheint mir dann ein Fehlverhalten des DPD-Programms zu sein.

Gruß,
O.

 

[liselotte]

Das scheint mir dann ein Fehlverhalten des DPD-Programms zu sein.

Mit ziemlicher Gewissheit, wir warten schon ewig auf ein UpDate
@owieortho Danke Deiner Erklärung ich hoffe so genau brauche ich das nie. Gar keine Zeit dafür.
MfG

 

[wex_stallion]

Lass es mich so sagen:

Ich hab mal händisch in myDPD eingegebene Adressen exportiert, in diesem exportierten csv mittels Texteditor einfache Datenänderungen vorgenommen und das geänderte Ding zum Verrecken nicht wieder ins myDPD reinkloppen können.

Wahrscheinlich bin ich nur zu doof, aber mit dem Tool kannste eigentlich nur arbeiten, wenn dir jemand die Stunden extra zahlt.

 

[liselotte]

Solchen Fall hatte ich noch nicht, dafür aber zig "Dubletten" obwohl sich ja alle Nase lang der Ansprechpartner ändert
Frohes Fest
ich muss noch die letzten Montage und den Freitag rabotten und dann gönne ich mir eine Pause
MfG