trennen bis zum leerzeichen

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: trennen bis zum leerzeichen
von: börni
Geschrieben am: 26.09.2003 11:40:17

hi leute,

folgendes problem:

ich hab in einer spalte die anschriften stehen (zb teststraße 102A/3). ich will
die komplette hausnummer in einer eigenen spalte.
natürlich sind bei jeder adresse die hausnummern unterschiedlich lang, d.h. nur z.b. mit =rechts(a2;5) funktionierts nicht. welche möglichkeit habe ich,
einfach von rechts bis zum leerzeichen abzufragen?

danke für die schnelle hilfe!

börni

Bild


Betrifft: AW: trennen bis zum leerzeichen
von: Reinhard
Geschrieben am: 26.09.2003 12:07:19

Hi börni,
siehe Hajo's Posting auf dieser seite:
AW: Text teilen - von Hajo_Zi am 25.09.2003 18:15:23
Gruß
Reinhard


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: Guido Palacios
Geschrieben am: 26.09.2003 12:32:40

Hallo Nochmal,

bei Strassennamen a la "Hans Werner Strasse 11" nimm die InStrRev- Funktion!!!
Dann hast Du keine Probleme mehr!!

CYA
Guido P


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: börni
Geschrieben am: 26.09.2003 12:36:34

hi leute,

vielen dank für eure flotten rückmeldungen!
guido hat recht, bei mehreren leerzeichen funktionert es nicht so einfach!

von der InStrRev-funktion hab ich schon mal gehört, hab aber leider keine
idee, wie es funktioniert! könnte mir das bitte noch jemand erklären?

wäre super!

danke nochmals
börni


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: Guido Palacios
Geschrieben am: 26.09.2003 21:52:58

Die gefällt mir besser!!


' ******************************************************************************
' Name : Public

Function InStrRev97()
    ' ******************************************************************************
    ' Datum            : 23.09.2003
    ' EntwicklerIn     : Guido Palacios
    ' Version          : Initial
    ' Kommentar        : Erstrealisierung
    ' ==============================================================================
    ' Beschreibung  : Gibt die Position des von hinten zuerst gefundenen Zeichens in
    '                 einem String zurueck
    '
    ' ==============================================================================
 
    Public 

Function InStrRev97(mystr As Variant, mydelim As String) As Long
    
    ' ------------------------------------------------------------------------------
    ' Variablendeklararion
    ' ------------------------------------------------------------------------------
    Dim i As Long
    Dim j As Long
    
    ' ------------------------------------------------------------------------------
    ' Beginn der eigentlichen Prozedur
    ' ------------------------------------------------------------------------------
    
    On Error GoTo msgerror
    
    InStrRev97 = 0
    j = Len(mydelim)
    
    For i = Len(mystr) To 1 Step -1
        If Mid(mystr, i, j) = mydelim Then
            InStrRev97 = i
            Exit Function
        End If
    Next i
    
    ' ------------------------------------------------------------------------------
    ' Fehlerbehandlung
    ' ------------------------------------------------------------------------------
    
msgerror:
    MsgBox Err.Number & Chr$(13) & Err.Description
    
    ' ------------------------------------------------------------------------------
    ' Ende der eigentlichen Prozedur
    ' ------------------------------------------------------------------------------
    End Function



Grüße
Guido P.


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: WernerB.
Geschrieben am: 26.09.2003 12:09:31

Hallo Börni,

wenn in Zelle A1 die Straße steht:
=RECHTS(A1;LÄNGE(A1)-FINDEN(" ";A1))


Viel Erfolg wünscht
WernerB.

P.S.: Das Forum lebt auch von den Rückmeldungen der Fragesteller an die Antworter (siehe Forums-FAQ).


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: Peter
Geschrieben am: 26.09.2003 12:27:23

Hallo Werner,
hoffentlich hat der Name der Str. nur ein Leerzeichen ...
(funktioniert bei z. B. "Dr. Richard Sorge Str. 75" nicht!!) ...
gibt es dafür eine bessere Lösung (Anzahl Leerzeichen variabel...)?
Gruß Peter


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: WernerB.
Geschrieben am: 26.09.2003 12:38:23

Hallo Peter,

nach meiner - unmaßgeblichen - Rechtschreibauffassung schreibt man solche Straßennamen so:
"Dr.-Richard-Sorge-Str. 75"
Damit hätte sich das Problem erledigt.

Spaß beiseite: In Sachen Excel-Formeln/Funktionen bin ich nicht der große Zampano.
Mit VBA gibt es seit Excel2000 (ich habe noch Excel97) eine Funktion mit der man bestimmte Zeichen in einem Text-String von hinten ("InStrRev" oder so ähnlich) suchen kann; damit sollte es klappen.
Aber hier wird vermutlich eine Formel-Lösung gesucht.


Gruß WernerB.


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: Peter
Geschrieben am: 26.09.2003 13:31:12

Hallo Werner,
... stimmt wohl bei derartigen Namen ... aber es gibt auch gültige Namen
mit mehreren Leerzeichen ("Altlandsberger Str. 100") ...
trotzdem Danke für den Hinweis - auch wenn ich ihn mit meinem
steinzeitlichen EXCEL'97 nicht umsetzen kann ...
Gruß Peter


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: WernerB.
Geschrieben am: 26.09.2003 13:51:35

Hallo Peter,

dem Manne kann geholfen werden: Man kann die neuere VBA-Funktion "InStrRev" für Excel97-Anwender in einer benutzerdefinierten VBA-Funktion nachbauen; vielleicht hilft sie Dir ja weiter.
Sie stammt nicht von mir, ich habe sie vor längerer Zeit hier im Forum gefunden:


Option Explicit

Function LastInStr(Text As String, Suchtext As String) As Integer
Dim Pos As Integer, OldPos As Integer
    Do
      OldPos = Pos
      Pos = InStr(Pos + 1, Text, Suchtext)
    Loop Until Pos = 0
    LastInStr = OldPos
End Function

Viel Erfolg wünscht
WernerB.

P.S.: Das Forum lebt auch von den Rückmeldungen der Fragesteller an die Antworter (siehe Forums-FAQ).


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: Peter
Geschrieben am: 26.09.2003 14:58:08

Hallo Werner,
diese Function gefällt mir gut! Danke!!
Wenn ich sie in ein Modul aufnehme, und nach Änderung eines Tabellenwertes
über Private Sub Worksheet_Change(ByVal Target As Excel.Range) aktiviere,
funktioniert es schrittweise (abgesehen davon, dass Schreibfehler wie z. B.
ein fehlendes Leerzeichen vor der Hausnummer noch nicht abgefangen werden).
Versuche ich aber dann die erhaltenen Werte an Target.Value oder
Target.Offset(0,1).Value zurückzugeben, startet das Makro erneut - und es
kommt zu einer Fehlermeldung. Gibt es einen Trick, durch den die Rückgabe
der geänderten Werte in die Tabelle nicht ihrerseits erneut die
Private Sub Worksheet_Change(ByVal Target As Excel.Range) startet?
Gruß Peter


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: WernerB.
Geschrieben am: 26.09.2003 15:07:54

Hallo Peter,

vor dem Zelleintrag:
Application.EnableEvents = False 'Ereignis ausschalten

danach:
Application.EnableEvents = True 'Ereignis einschalten


Gruß WernerB.


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: Peter
Geschrieben am: 26.09.2003 15:23:47

Danke!!
Die richtige Schreibweise der Str. vorausgesetzt (aber da bastele ich noch
weiter dran ...) funktioniert es jetzt:

Beispiel


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
r = Target.Row
s = Target.Column
If Target.Value = "" Then
    Exit Sub
Else
    strasse = Mid(Target.Value, 1, LastInStr(Target.Value, " ") - 1)
    nr = Mid(Target.Value, LastInStr(Target.Value, " ") + 1, 99)
    Application.EnableEvents = False
    Cells(r, s).Value = strasse
    Cells(r, s + 1).Value = nr
    Application.EnableEvents = True
End If
End Sub


und Eintrag in einem Modul:

Function LastInStr(Text As String, Suchtext As String) As Integer
Dim Pos As Integer, OldPos As Integer
Do
OldPos = Pos
Pos = InStr(Pos + 1, Text, Suchtext)
Loop Until Pos = 0
LastInStr = OldPos
If LastInStr = 0 Then
LastInStr = 1
End If
End Function


Gruß Peter


Bild


Betrifft: oder mit: Daten / Text in Spalten______o.T.
von: Günther Oppold
Geschrieben am: 26.09.2003 12:11:39

o.T. = ohne Text


Bild


Betrifft: AW: trennen bis zum leerzeichen
von: Guido Palacios
Geschrieben am: 26.09.2003 12:30:08

Hi,

wüsste wie du's mit VBA lösen könntest!
So:
-------------------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim laenge As Long
Dim start As Long
Dim Nr As Long
Dim Strasse As String
If InStr(Target.Value, " ") = 0 Then
    Exit Sub
Else
    laenge = InStr(Target.Value, " ")
    If Target.Column = 2 Then
        Strasse = Trim(Left(Target.Value, laenge))
        Nr = Mid(Target.Value, laenge + 1)
        Cells(Target.Row, 2) = Strasse
        Cells(Target.Row, 3) = Nr
    End If
End If
End Sub

-------------------------------------------------------------

Anbei noch eine Beispieldatei: Klicke einfach auf die Strassennamen, die Nr wird dann in die Zelle daneben geschrieben und der Strassenname wird gekürzt!

https://www.herber.de/bbs/user/1171.xls

Grüße
Guido P


 Bild

Beiträge aus den Excel-Beispielen zum Thema " trennen bis zum leerzeichen"