Extract data between 2 comas

[King21]

I have a field "Address" with the address format as "123 Something Rd, Somewhere, NSW 3456".

I would like to be able to extract the location name, which is between 2 comas, using the above example have the location name "Somewhere" copied the another field called "Location"

I have tried Mid, Left, Right expressions but to no avail

Thanks in advance

 

[theDBguy]

Definitely need to use those, but also need to include InStr() to locate the commas.

 

[arnelgp]

i would create a function so you can test if there is at least 2 comma on the string:

Public Function WordsBetween1stAnd2ndDelim(ByVal pString As Variant, Optional ByVal delim As String = ",") As String
    Dim iCountDelim
    pString = pString & ""
    If Len(pString) < 1 Then
        Exit Function
    End If
    iCountDelim = dhCountIn(pString, delim)
    If iCountDelim < 2 Then
        Exit Function
    End If
    WordsBetween1stAnd2ndDelim = Trim$(Split(pString, ",")(1))
End Function

' Counting the Number of Times a Substring Appears
' VBA handbook
Public Function dhCountIn(ByVal strText As String, ByVal strFind As String, _
Optional ByVal lngCompare As VbCompareMethod = vbDatabaseCompare) As Long
Dim lngCount As Long
Dim lngPos As Long
    ' If there's nothing to find, there surely can't be any
    ' found, so return 0.
    If Len(strFind) > 0 Then
        lngPos = 1
        Do
            lngPos = InStr(lngPos, strText, strFind, lngCompare)
            If lngPos > 0 Then
                lngCount = lngCount + 1
                lngPos = lngPos + Len(strFind)
            End If
        Loop While lngPos > 0
    Else
        lngCount = 0
    End If
    dhCountIn = lngCount
End Function

?WordsBetween1stAnd2ndDelim("123 Something Rd, Somewhere, NSW 3456")
result: Somewhere

 

[King21]

arnelgp

Thanks for the reply

Do I put the function into a module, if so how would I call it

 

[arnelgp]

Do I put the function into a module, if so how would I call it

yes.

you need to put the extracted word(s) to Location field, so call it from an Update query:

Update [yourTable] Set [Location] = WordsBetween1stAnd2ndDelim([Address], ",")

 

[Pat Hartman]

You can use the split function.

MyArray = Split(Address, ",")
City = MyArray(2)

or MyArray(1) if your array is zero based.

 

[MarkK]

If you are going to call a function, you might as well use VBA.Split()....

Public Function TextBetween1stAnd2ndComma(Text As String) As String
    TextBetween1stAnd2ndComma = Split(Text, ",")(1)
End Function

...which, it looks like, Pat already suggested. : )

 

[King21]

Apologies for not replying earlier.

arnelgp, thanks for your suggestion it works great.

Thanks to everyone who replied