• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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
0
Fordraiders
Asked:
Fordraiders
  • 5
  • 4
  • 4
  • +1
2 Solutions
 
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 LissRetired ProgrammerCommented:
Or Split()

Dim strParts() As String

strParts = Split(s, " ")

Open in new window

strParts(2) will have the value after the second space.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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
 
FordraidersAuthor Commented:
Thanks to all.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now