Solved

extract string from textbox after second space

Posted on 2014-07-25
14
170 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
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 46

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
 
LVL 46

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 46

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

910 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

20 Experts available now in Live!

Get 1:1 Help Now