Excel - eigene Funktion #Wert!

Hallo,

ich habe eine Excel-Arbeitsmappe, in der mein Kollege eine Spalte hat, in welcher „PLZ und Ort“ vorhanden sind.
Nun ist das Problem, dass die eingetragenen Werte (wenn er sie von irgendwo her nur übertragen hat) nicht nur „normal“ aufgebaut sind (PLZ Leerzeichen Ort) sondern auch mal „Leerzeichen Leerzeichen PLZ Leerzeichen Ort“ oder vorn nur mit einem Leerzeichen. Auch kann es sein, dass zwischen PLZ und Ort nicht nur ein Leerzeichen ist (VBA: Asc(32)) sondern auch mal das Zeichen (VBA: Asc(160), von dem ich gar nicht so richtig weiß, was das ist.

Der Kollege hätte gern PLZ und Ort getrennt in jeweils einer Spalte.

Frage 1: Hat dazu jemand eine Idee? Ich bin irgendwie nicht weiter gekommen durch dieses Zeichen 160. Trim kenne ich.

Aus diesem Grund habe ich eine eigene Funktion geschrieben namens PLZ und Ort, welcher mir das per VBA auswertet (Code von der Function „Ort“ am Ende vom Text).
Grundsätzlich funktionieren beide Makros so, wie ich mir das vorstelle.

Jetzt kommt noch folgendes dazu. Ich arbeite zusätzlich noch mit Worksheets_Change um eine Zelle in einer Spalte abzufangen und um doppelte Werte zu vermeiden.
Somit arbeite ich auch mit „Application.Calculate“ etc. um das am Anfang auszuschalten und am Ende wieder einzuschalten.

Seit dem „spinnt“ komischerweise die „Ort“-Function in dem das Ergebnis eine Fehlermeldung ("#Wert!") bringt.
In der Function zeigt er am Schluss den richtigen Inhalt an. Daran kann es also eigentlich nicht liegen. Habe auch „Ort“ als String definiert. die Zellen sind als „Standard“ definiert. Es gibt keine bedingte Formatierung oder so.

Frage 2: woran kann das liegen?

um den nachfolgenden Functions-Code zu verstehen (weiß nicht, ob ich hier eine Tabelle anhängen kann) folgende Erklärung:

  • in Spalte AK steht „PLZ und Ort“ zusammen drin
  • in Spalte AL steht meine Function „PLZ“, welche absolut problemlos funktioniert und korrekte Werte zurück meldet als PLZ.
  • in Spalte AM steht „=Ort(AK2)“ (bis „=Ort(AK170)“)

Die Function „Ort“ lautet:

Function Ort(Inhalt As String) As String
'liest aus einer Zeichenkette den Ort aus
'wichtig: vor dem Ort dürfen keine anderen Buchstaben (außer Leerzeichen) stehen
'Das Auslesen endet, sobald nach einer Buchstabenkette ein numerisches Zeichen folgt

Dim i As Integer
Dim Zeichen As String
Dim BoolBeginn As Boolean
Dim PLZ As String
Dim BoolZeichen As Boolean

Application.EnableEvents = False

If Inhalt = Empty Then
    Ort = Empty
    Exit Function
End If

PLZ = Cells(ActiveCell.Row, ActiveCell.Column - 1).Value

For i = Len(PLZ) + 1 To Len(Inhalt)
    Zeichen = Mid(Inhalt, i, 1)
    If Not (IsNumeric(Zeichen)) And Asc(Zeichen) <> 32 And Asc(Zeichen) <> 160 Then
        BoolBeginn = True
        Ort = LCase(Right(Inhalt, Len(Inhalt) - i + 1))
        Ort = GrossKlein(Ort) 'passt den Inhalt an die Groß- und Kleinschreibung an
        Exit For
    ElseIf IsNumeric(Zeichen) And BoolBeginn = True Then
        Exit For
    End If
Next

Call Anzeigen

End Function

'-------------------------
Sub Anzeigen()
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Worksheets(1).EnableCalculation = True
Application.Calculate
End Sub

„PLZ = …“ liefert die PLZ aus der vorher stehenden Zelle zurück
die Function „GrossKlein(Ort)“ passt nur die Buchstaben an Groß- und Kleinschreibung an, funktioniert und deshalb habe ich den Code hier weg gelassen.

Wäre cool, wenn jemand eine Idee oder Lösung hat um evtl Frage 2 und oder Frage 1 zu klären. Im letzten Fall könnte ich ja die eigene Funktion weg lassen.

Danke schon mal fürs durchlesen
Tobi@s

Hallo,

Das ist das geschütztes Leerzeichen aus der ASCII-Tabelle.

Ich würde die Originaldaten z.B. in Spalte A schreiben, Spalte B als Hilfsspalte benutzen um die Daten zu bereinigen (entfernen aller ungewollter Leerzeichen) und in den Spalten C und D jeweils PLZ und Ort extrahieren:

Formel B1:

=GLÄTTEN(WECHSELN(A1;ZEICHEN(160);ZEICHEN(32)))

Formel C1:

=LINKS(B1;SUCHEN(" ";B1)-1)

Formel D1:

=RECHTS(B1;LÄNGE(B1)-SUCHEN(" ";B1))

Gruß
Tobias

Hi,

hast Du und Dein Kollege daran gedacht, daß es im Osten die Nuller-PLZ gibt?
(Das war damals eine blöde Idee von der Post!)

Ich hab’s in Excel vor grauer Vorzeit nicht hingekriegt und mich dafür entschieden, PLZ und Ort in einem Feld zu haben.
Wenn man keine alphabetische Sortierung nach Ortsnamen braucht, muß man ja auch nicht unbedingt trennen.

Gruß

Hallo,

danke für den Gedanken mit den „Ost“-PLZ.
Da ich im 0-PLZ-Bereich wohne, bin ich es (leider) gewohnt, immer dran denken zu müssen.

Aber - wie auch gesagt - das mit der PLZ funktioniert 100%, nur das mit dem Ort haut nicht hin.
Ich schau mir mal den anderen Beitrag noch an.

Gruß Tobi@s

Hallo Tobias :smile:
ich habe deine Formeln eingegeben. Auf den Gedanken mit dem Glätten und Wechseln bin ich nicht gekommen. Der Rest ist dann wieder einfach (kenne sowas ja zu Genüge durch VBA).

So kann ich doch meine Funktionen in die Tonne klopfen und auf Excel-Mittel zurück greifen.

Ich habe jedoch die Formeln noch mal zusammengefasst (da andere drin arbeiten, will ich das sauber und schmal halten).
Was ich noch ergänzt habe (da das Problem mit auftauchte), ist, dass er den jeweils ersten Buchstaben groß schreibt (=Gross2()). Auch ergänzt habe ich noch, dass er „“ eintragen soll, wenn in der Original-Zelle gar nichts steht.

Falls jemand ein ähnliches Problem hat, hier mal meine Zusammenfassung:

=WENN(LÄNGE(AK2)>0;GROSS2(RECHTS(GLÄTTEN(WECHSELN(AK2;ZEICHEN(160);ZEICHEN(32)));LÄNGE(GLÄTTEN(WECHSELN(AK2;ZEICHEN(160);ZEICHEN(32))))-SUCHEN(" „;GLÄTTEN(WECHSELN(AK2;ZEICHEN(160);ZEICHEN(32))))));“")

Gruß Tobias