Extract 2nd word of string

Thanks:  0
Likes:  0

# Thread: Extract 2nd word of string

1. ## 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. InStr() replace Find() in Access.

HTH

Peter

3. ## 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. I think that to do that sort of split you will need to write a custom function in VBA and call that.

Peter

5. 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?

6. ## Re: Extract 2nd word of string

I need it split into two fields.

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

peter

8. ## Re: Extract 2nd word of string

As a number.

9.
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

## User Tag List

#### Posting Permissions

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