Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Extract Text (2003)

    I have a various strings of text in the format of 'abcdef V abc'. The number of characters before, and after the V, can vary. I want to create two fields. One containing the text before the V, and another one after. How do I do this, when I can't determine the length of the string?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Text (2003)

    Create a query based on the table. Add two calculated columns:

    FirstPart: Left([FieldName], InStr([FieldName], " V ")-1)

    and

    LastPart: Mid([FieldName], InStr([FieldName], " V ")+3)

    where FieldName is the name of your field.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extract Text (2003)

    Thanks Hans.

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extract Text (2003)

    I've just discovered that some of the text fields have nothing before the V, and some nothing after. This is bringing up and error. Is there a simple way round this?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Text (2003)

    Try these considerably more complicated expressions:

    FirstPart: IIf(Left([FieldName],2)="V ",Null,IIf(Right([FieldName],2)=" V",Left([FieldName],InStr([FieldName]," V")-1),Left([FieldName],InStr([FieldName]," V ")-1)))

    LastPart: IIf(Right([FieldName],2)=" V",Null,IIf(Left([FieldName],2)="V ",Mid([FieldName],3),Mid([FieldName],InStr([FieldName]," V ")+3)))

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extract Text (2003)

    Thanks Hans. It's working fine, for the moment.

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Text (2003)

    A general solution to this sort of problem are the following 2 functions which return the first part of a string up to a defined character or characters & the part after the character or characters.

    Function GetFirstWord(strIn As String, chrDelimit As String) As String
    ' Parameters : strIn - string to search
    ' chrDelimit - character delimiter
    ' if delimiter is not found whole string is returned
    Dim strTmp As String
    Dim intPos As Integer
    Dim strFirstName
    Dim strLastName

    strTmp = Trim$(strIn)
    intPos = InStr(strTmp, chrDelimit)

    If intPos = 0 Then
    GetFirstWord = strTmp
    Else
    GetFirstWord = left$(strTmp, intPos - 1)
    strLastName = Mid(strTmp, intPos + 1)
    End If

    End Function

    Function GetLastWord(strIn As String, chrDelimit As String) As String
    ' Parameters : strIn - string to search
    ' chrDelimit - character delimiter
    ' if delimiter is not found null is returned
    Dim strTmp As String
    Dim intPos As Integer
    Dim strFirstName
    Dim strLastName

    strTmp = Trim$(strIn)
    intPos = InStr(strTmp, chrDelimit)

    If intPos = 0 Then
    GetLastWord = ""
    Else
    GetLastWord = Mid(strTmp, intPos + Len(chrDelimit))
    End If

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •