Results 1 to 9 of 9

Extract 2nd word of string

This is a discussion on Extract 2nd word of string within the Microsoft Access forums, part of the Question Forums category; In excel I developed a formula to extract the second word of a text string. Cell A1 = "The standard ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Location
    Gainesville, FL
    Posts
    40

    Default Extract 2nd word of string

    In excel I developed a formula to extract the second word of a text string.

    Cell A1 = "The standard response"

    Cell B1 = MID(A2, FIND(" ",A2,1)+1, FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,FIND(" ",A2,1)))) retruns "standard"

    Can this be done in a select query in access. I do not have a "Find" function in access to determine the space location.

    Thanks

  2. #2
    Board Regular
    Join Date
    Aug 2003
    Location
    Maidstone, Kent UK
    Posts
    1,470

    Default

    InStr() replace Find() in Access.

    HTH

    Peter

  3. #3
    New Member
    Join Date
    Aug 2002
    Location
    Gainesville, FL
    Posts
    40

    Default Re: Extract 2nd word of string

    Works great! One other question, how can I further split a string between a numeric value and a letter?

    10,000u/ml split into "10,000" and "u/ml". The units can vary "u/ml", "mcg/ml", etc. so I need a general approach.

    I thought I could use the InStr function with "Like[a-z]".

  4. #4
    Board Regular
    Join Date
    Aug 2003
    Location
    Maidstone, Kent UK
    Posts
    1,470

    Default

    I think that to do that sort of split you will need to write a custom function in VBA and call that.

    Peter

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Posts
    354

    Default

    Yes, you could write a VBA function. Are you looking to split this into two separate fields or to simply put a space between your numeric value and your text?
    I love pants

  6. #6
    New Member
    Join Date
    Aug 2002
    Location
    Gainesville, FL
    Posts
    40

    Default Re: Extract 2nd word of string

    I need it split into two fields.

  7. #7
    Board Regular
    Join Date
    Aug 2003
    Location
    Maidstone, Kent UK
    Posts
    1,470

    Default

    will the 10,000 need to be returned as a number or in the format "10,000"

    peter

  8. #8
    New Member
    Join Date
    Aug 2002
    Location
    Gainesville, FL
    Posts
    40

    Default Re: Extract 2nd word of string

    As a number.

  9. #9
    Board Regular
    Join Date
    Aug 2003
    Location
    Maidstone, Kent UK
    Posts
    1,470

    Default

    Ok. The following two functions should be able to get your info for you. I have not added any data checking or error trapping to the code!!

    Code:
    Function GetNum(strIn As String) As Double
    Dim j As Integer
    j = 1
    Do While j <> Len(strIn)
    If Not IsNumeric(Mid(strIn, j, 1)) And Not Mid(strIn, j, 1) = "," And Not Mid(strIn, j, 1) = "." Then Exit Do
    j = j + 1
    Loop
    GetNum = CDbl(Left(strIn, j - 1))
    End Function
    
    
    Function GetStr(strIn As String) As String
    Dim j As Integer
    j = 1
    Do While j <> Len(strIn)
    If Not IsNumeric(Mid(strIn, j, 1)) And Not Mid(strIn, j, 1) = "," And Not Mid(strIn, j, 1) = "." Then Exit Do
    j = j + 1
    Loop
    GetStr = Right(strIn, Len(strIn) - j + 1)
    End Function
    Peter

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com