Remove everything including the comma from the last comma of a string. 1

[Moss100]

Hello,

I need to remove everything from the last comma of a string (including the last comma)

So for example I have an address

20 Bright Street, Walsham, England, CH41 0AD

And I need:

20 Bright Street, Walsham, England

If anyone is able to help me with this it would be great.

AND

If anyone fancies a further challenge, the actual data I am working with appears as

20 Bright Street {Smith}, Walsham, England, CT85 8AT

What ultimately I want to do is remove everything bewteen the brackets "{" & "}" (including the brackets)
AND
Also remove everything after the last comma, including the comma, so the end result is:

20 Bright Street, Walsham, England

Many thanks Mark

 

[Andrzejek]

I am sure thee is a better, 'fancier' way to do it...

Option Explicit

Sub Test()
Dim str As String

str = "20 Bright Street {Smith}, Walsham, England, CT85 8AT"

Debug.Print RemoveStuff(str)

End Sub

Function RemoveStuff(ByRef strIn As String) As String
Dim ary() As String
Dim i As Integer
Dim strOut As String

If InStr(strIn, ",") Then
    ary = Split(strIn, ",")
    For i = LBound(ary) To UBound(ary) - 1
        If i = LBound(ary) Then
            strOut = ary(i)
        Else
            strOut = strOut & "," & ary(i)
        End If
    Next i
End If

If InStr(strOut, "{") And InStr(strOut, "}") Then
    strOut = Left(strOut, InStr(strOut, "{") - 2) & Mid(strOut, InStr(strOut, "}") + 1)
End If

RemoveStuff = strOut

End Function

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Moss100]

You are a genius - thank you!

 

[Andrzejek]

Yes, I am. I can't help it

Here is a little shorter/different version of the Function:

Function RemoveStuff(ByRef strIn As String) As String
Dim i As Integer
Dim strOut As String

If InStr(strIn, ",") Then[blue]
    For i = Len(strIn) To 1 Step -1
        If Mid(strIn, i, 1) = "," Then
            strOut = Left(strIn, i - 1)
            Exit For
        End If
    Next i[/blue]
End If

If InStr(strOut, "{") And InStr(strOut, "}") Then
    strOut = Left(strOut, InStr(strOut, "{") - 2) & Mid(strOut, InStr(strOut, "}") + 1)
End If

RemoveStuff = strOut

End Function

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Or ...

[COLOR=blue]Function RemoveStuff(ByRef strIn As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "( {.*}|,[^,]*?$)"
        .Global = True
        RemoveStuff = .Replace(strIn, "")
    End With
End Function[/color]

 

[dhookom]

I like creating functions but you could use a fairly simple expression:

Left("20 Bright Street, Walsham, England, CH41 0AD",InstrRev("20 Bright Street, Walsham, England, CH41 0AD",",")-1)

If your field name is [Full Address]:

Left([Full Address],InstrRev([Full Address],",")-1)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

My preferred solution would be:
Split the 'Full Address' into its separate fields in the table:

[pre]
Address || FieldX || FieldY || FieldZ || FieldQ
-----------------||--------||---------||---------||---------
20 Bright Street || Smith || Walsham || England || CT85 8AT
123 Main Street || Brown || Dundee || Scotland|| ABC XYZ
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

I totally agree with using multiple fields for multiple data items. It's super easy to combine them as needed.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[strongm]

Duane, your one liner does not address "remove everything bewteen the brackets "{" & "}" (including the brackets)

 

[dhookom]

Strongm,
You are correct. I was a bit trigger happy.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

But it does replace my 6 lines of code with 1 line of his.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson