Solved

extract string from textbox after second space

Posted on 2014-07-25
14
212 Views
Last Modified: 2014-07-30
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
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40219881
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40219938
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40219968
Or Split()

Dim strParts() As String

strParts = Split(s, " ")

Open in new window

strParts(2) will have the value after the second space.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 48

Expert Comment

by:Martin Liss
ID: 40219974
Or

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

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40220046
Beware of duplicate spaces when using the Split() function.  If performance is an issue, the Split() function is faster than the regexp object.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40220075
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40220132
Martin, the Split function only returns "groceries".

But thanks for reminding me about the "1000" element; old habits die hard. :-)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40220147
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40220242
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40220354
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
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 250 total points
ID: 40220379
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40220396
Martin, the Split function only returns "groceries".
Oops, you're absolutely right.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 250 total points
ID: 40220812
@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
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 40229658
Thanks to all.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question