Solved

extract string from textbox after second space

Posted on 2014-07-25
14
164 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
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Or Split()

Dim strParts() As String

strParts = Split(s, " ")

Open in new window

strParts(2) will have the value after the second space.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Or

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

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
Martin, the Split function only returns "groceries".

But thanks for reminding me about the "1000" element; old habits die hard. :-)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

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

Accepted Solution

by:
aikimark earned 250 total points
Comment Utility
@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
Comment Utility
Thanks to all.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now