none
Spliting on delimiter in access mquery RRS feed

  • Question

  • I would like to split a string based on a delimiter like "-" (or multiple characters if possible). I have attempted to use the Split function unsuccessfully. For example:

    I want to be able to take a string like AB - CD and split it to obtain CD.  Is this possible?

    Thursday, June 6, 2019 7:49 PM

Answers

  • You cannot use the Split function directly, but you can create a custom VBA function that uses Split, and use that function in a query. For example:

    Function MySplit(expression, delimiter As String, num As Long)
        Dim a() As String
        MySplit = Null
        If Not IsNull(expression) Then
            a = Split(expression, delimiter)
            If num >= 0 And num <= UBound(a) Then
                MySplit = a(num)
            End If
        End If
    End Function

    Use like this in a query:

    Part2: MySplit([NameOfField]," - ",1)

    Keep in mind that the result of Split is zero-based, so the above will return the 2nd element of the result of Split (if available).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, June 6, 2019 8:28 PM
  • If you want the text to the right of the – you can use three functions combined in a query.

    First find the location of the – using

    InStr([myString],"-")

    Then use Mid(«string», «start», «length») to get the string

    Mid([myString], InStr([myString],"-")+1)  adding the 1 moves the «start» position to the right of the -

    And last use Trim(«string»)  to remove leading or trailing spaces

    Expr1: Trim(Mid([myString],InStr([myString],"@")+1))


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Thursday, June 6, 2019 8:57 PM
  • The following is a little function I've used for many years to return a substring by position from a delimited list:

    Function MySplit(strIn As String, iPos As Integer, strDel As String)

    Const OUT_OF_RANGE = 9
    Dim varSplit As Variant
       On Error GoTo MySplit_Error

        varSplit = Split(strIn, strDel)
        MySplit = varSplit(iPos - 1)

    MySplit_Exit:
       Exit Function

    MySplit_Error:
        Select Case Err.Number
            Case OUT_OF_RANGE
            MySplit = Null
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MySplit"
        End Select
        Resume MySplit_Exit
        
    End Function

    You can see how it works in the immediate window:

    ? Trim(MySplit("The cat - sat on - the mat",1,"-"))
    The cat
    ? Trim(MySplit("The cat - sat on - the mat",2,"-"))
    sat on
    ? Trim(MySplit("The cat - sat on - the mat",3,"-"))
    the mat

    The Trim function is called in the above examples to strip off the leading and trailing spaces.  Alternatively the spaces can be included in the strDel argument as part of the delimiter:

    ? MySplit("The cat - sat on - the mat",3," - ")
    the mat

    If a value is passed into the function as the iPos argument which is out of range as the subscript, then a Null is returned:

    ? MySplit("The cat - sat on - the mat",4," - ")
    Null

    Ken Sheridan, Stafford, England

    Friday, June 7, 2019 11:23 AM

All replies

  • You cannot use the Split function directly, but you can create a custom VBA function that uses Split, and use that function in a query. For example:

    Function MySplit(expression, delimiter As String, num As Long)
        Dim a() As String
        MySplit = Null
        If Not IsNull(expression) Then
            a = Split(expression, delimiter)
            If num >= 0 And num <= UBound(a) Then
                MySplit = a(num)
            End If
        End If
    End Function

    Use like this in a query:

    Part2: MySplit([NameOfField]," - ",1)

    Keep in mind that the result of Split is zero-based, so the above will return the 2nd element of the result of Split (if available).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, June 6, 2019 8:28 PM
  • If you want the text to the right of the – you can use three functions combined in a query.

    First find the location of the – using

    InStr([myString],"-")

    Then use Mid(«string», «start», «length») to get the string

    Mid([myString], InStr([myString],"-")+1)  adding the 1 moves the «start» position to the right of the -

    And last use Trim(«string»)  to remove leading or trailing spaces

    Expr1: Trim(Mid([myString],InStr([myString],"@")+1))


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Thursday, June 6, 2019 8:57 PM
  • The following is a little function I've used for many years to return a substring by position from a delimited list:

    Function MySplit(strIn As String, iPos As Integer, strDel As String)

    Const OUT_OF_RANGE = 9
    Dim varSplit As Variant
       On Error GoTo MySplit_Error

        varSplit = Split(strIn, strDel)
        MySplit = varSplit(iPos - 1)

    MySplit_Exit:
       Exit Function

    MySplit_Error:
        Select Case Err.Number
            Case OUT_OF_RANGE
            MySplit = Null
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MySplit"
        End Select
        Resume MySplit_Exit
        
    End Function

    You can see how it works in the immediate window:

    ? Trim(MySplit("The cat - sat on - the mat",1,"-"))
    The cat
    ? Trim(MySplit("The cat - sat on - the mat",2,"-"))
    sat on
    ? Trim(MySplit("The cat - sat on - the mat",3,"-"))
    the mat

    The Trim function is called in the above examples to strip off the leading and trailing spaces.  Alternatively the spaces can be included in the strDel argument as part of the delimiter:

    ? MySplit("The cat - sat on - the mat",3," - ")
    the mat

    If a value is passed into the function as the iPos argument which is out of range as the subscript, then a Null is returned:

    ? MySplit("The cat - sat on - the mat",4," - ")
    Null

    Ken Sheridan, Stafford, England

    Friday, June 7, 2019 11:23 AM