extract string from textbox after second space

VBA excel 2010.

I need a variable that will show me the string from a textbox after the second space.
Example:

dim s as string


we need groceries and gas

The string should look like:
s = groceries and gas


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
If you let strTest = "we need groceries and gas" then use this to extract all text from second space on:
s = Mid(strTest, InStr(InStr(1, strTest, " ") + 1, strTest, " ") + 1, 1000)

Open in new window


Regards,
-Glenn
0
aikimarkCommented:
You should consider regular expressions
Option Explicit

dim s as string
Dim oRE as Object
Dim oMatches as Object, oM as Object
set oRE = createobject("vbscript.regexp")
oRE.Pattern = "(\w+)"
s = "groceries and gas"
set oMatches = oRE.Execute(s)
For Each oM in oMatches(0).Submatches
   Debug.Print oM
Next

Open in new window

Here is a function that will allow you to get the nth-word
Function GetNthWord(parmString As String, parmWord As Long) As String
    dim s as string
    Dim oRE as Object
    Dim oMatches as Object, oM as Object
    If oRE is Nothing Then
        set oRE = createobject("vbscript.regexp")
        oRE.Pattern = "(\w+)"
    End If
    s = parmString 
    set oMatches = oRE.Execute(s)
    GetNthWord = oMatches(0).Submatches(parmWord - 1)
End Function

Open in new window

0
Martin LissOlder than dirtCommented:
Or Split()

Dim strParts() As String

strParts = Split(s, " ")

Open in new window

strParts(2) will have the value after the second space.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Martin LissOlder than dirtCommented:
Or

Dim strParts() As String
MyResult = Split(s, " ")(2)

Open in new window

0
aikimarkCommented:
Beware of duplicate spaces when using the Split() function.  If performance is an issue, the Split() function is faster than the regexp object.
0
Martin LissOlder than dirtCommented:
As long as we're helping each other I'll mention that the 1000 in an algorithm like the following isn't necessary.

s = Mid(strTest, InStr(InStr(1, strTest, " ") + 1, strTest, " ") + 1, 1000)

And so this does the same in that it returns everything after the starting point.

s = Mid(strTest, InStr(InStr(1, strTest, " ") + 1, strTest, " ") + 1)
0
Glenn RayExcel VBA DeveloperCommented:
Martin, the Split function only returns "groceries".

But thanks for reminding me about the "1000" element; old habits die hard. :-)
0
aikimarkCommented:
0
Glenn RayExcel VBA DeveloperCommented:
aikimark:  I tried running your UDF (GetNthWord) and get a #VALUE! error.  When I converted the code into a regular subroutine:
 
Sub GetNthWord2()
    Dim parmString As String
    Dim parmWord As Long
    parmString = "we got groceries and gas"
    parmWord = 3
    Dim s As String
    Dim oRE As Object
    Dim oMatches As Object, oM As Object
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Pattern = "(\w+)"
    End If
    s = parmString
    Set oMatches = oRE.Execute(s)
    Debug.Print oMatches(0).submatches(parmWord - 1)
End Sub

Open in new window

I get an error on the final line (run-time error 5).  Any idea what's happening?
0
aikimarkCommented:
Thanks, Glenn.  I did not properly transfer test code into the code snippet.  The matches collection should have been used, not the submatches.  Corrected code follows.
Function GetNthWord(parmString As String, parmWord As Long) As String
    dim s as string
    Dim oRE as Object
    Dim oMatches as Object
    If oRE is Nothing Then
        set oRE = createobject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = "(\w+)"
    End If
    s = parmString 
    set oMatches = oRE.Execute(s)
    GetNthWord = oMatches(parmWord - 1)
End Function

Open in new window

Your (non-function) code test should look like this:
Sub GetNthWord2()
    Dim parmString As String
    Dim parmWord As Long
    parmString = "we got groceries and gas"
    parmWord = 3
    Dim s As String
    Dim oRE As Object
    Dim oMatches As Object, oM As Object
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = "(\w+)"
    End If
    s = parmString
    Set oMatches = oRE.Execute(s)
    Debug.Print oMatches(parmWord - 1)
End Sub

Open in new window

0
Glenn RayExcel VBA DeveloperCommented:
Thanks, aikimark.

Unfortunately, this function only returns "groceries", not "groceries and gas" as shown in the OP. :-(

fordraiders: this is the simplest method I can think of to return all text to the right of the second space in a string:
s = Mid(strTest, InStr(InStr(1, strTest, " ") + 1, strTest, " ") + 1)

Open in new window


Regards,
-Glenn
0
Martin LissOlder than dirtCommented:
Martin, the Split function only returns "groceries".
Oops, you're absolutely right.
0
aikimarkCommented:
@Glenn

I am picking out the individual words

The solution is this:
s="we need groceries and gas"
Debug.Print Split(s, " ", 3)(2)

Open in new window

If your string might have multiple spaces, the solution looks like this:
s=" we   need groceries   and gas "
Debug.Print Split(Replace(Trim(s), "  ", " "), " ", 3)(2)

Open in new window

or (worst case scenario) looks like this
s=" we   need groceries   and         gas "
s = Trim(s)
Do Until Instr(s, "  ") = 0
    s = Replace(s, "  ", " ")
Loop
Debug.Print Split(s, " ", 3)(2)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thanks to all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.