Return Characters to left of final dash in an address 2

[Moss100]

Hello I am trying to clean up an address field.

The addresses are in one field (Address1) and typically have a format of:

11 White Street - Ft1 - Konopka
28 Brown Road - Smith

I basically want to separate the address from the name.

So in a query I would like:

1. A field with all the text on the left of the last "-"
2. A field with all the text on the right of the last "-"

To solve requirement 1, I have tried:

AddressOnly: Left([Address1],InStr([Address1],"-")-1)

But this does not work where there is more than one "-"

Many thanks Mark

 

[SkipVought]

Hi,

I'd write a user defined VBA Function...

Function SubStr(sValue, sChr, sFLNum)
'sValue: the string you want to parse
'sChr: the delimiter character
'sFLNum: F first, L last Number any number from 1 to the max number of delimiter character

    Select Case sFLNum
        Case "F"
            SubStr = Split(sValue, sChr)(0)
        Case "L"
            SubStr = Split(sValue, sChr)(UBound(Split(sValue, sChr)))
        Case Else
            If IsNumeric(sFLNum) Then
                SubStr = Split(sValue, sChr)(Val(sFLNum) - 1)
            End If
    End Select
    SubStr = Trim(SubStr)
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

You could also flip your string (Address1) which will give you the name first, parse it, and flip it back

Sub test()

Dim sA As String
Dim sRev As String

sA = "11 White Street - Ft1 - Konopka"[green]
'sA = "Brown Road - Smith"[/green]

sRev = StrReverse(sA)

Debug.Print "Address is: " & Replace(sA, " - " & Trim(StrReverse(Split(sRev, "-")(0))), "")
Debug.Print "Name is: " & Trim(StrReverse(Split(sRev, "-")(0)))

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[Moss100]

Thanks. Sorted

 

[strongm]

And just for fun with regular expressions:

[blue]Public Sub example()
    Dim sA As String
    sA = "11 White Street - Ft1 - Konopka"
    
    With CreateObject("vbscript.regexp")
        .Pattern = "(.*) - (.*$)"
         If .Test(sA) Then
            With .Execute(sA).Item(0)
                Debug.Print "Address is: " & .SubMatches(0)
                Debug.Print "Name is: " & .SubMatches(1)
            End With
        End If
    End With

End Sub[/blue]