How do I select N words from a string of words?

[David Anderson]

I'm looking for a VBA function for use in Access 2003 that will let me select a specified number of words from a string (counting from the left), where one or more blank spaces are used to separate each word from one another. The result should only have single blank space separators.

For example, if such a function were called SelectWords then I would want SelectWords("Dog Cat Badger Fox Mouse", 3) to return the result of "Dog Cat Badger".

Any suggestions would be much appreciated.

David

 

[RuralGuy]

You will need to create your own public UDF (user defined function) in a standard module.

 

[David Anderson]

You will need to create your own public UDF (user defined function) in a standard module.

Yes, I already know how to do that. My problem is that I don't know a slick VBA solution to generate the required output from a given string of text.

David

 

[Mr. B]

David,

I'm looking for a VBA function for use in Access 2003 that will let me select a specified number of words from a string

Can you explain just where you expect to "select" these words?

 

[Brianwarnock]

a simple function will do it, but this assumes atleast 4 words in the original string, if this may not be the case you will need to check if the value returned is zero for any space and take action accordingly.

Function threewords(astring As String) As String
Dim space1%, space2%, space3%
space1 = InStr(1, astring, " ")
space2 = InStr(space1 + 1, astring, " ")
space3 = InStr(space2 + 1, astring, " ")
threewords = Left(astring, space3 - 1)
End Function

Brian

Ah! just noticed that you want to vary the number of words chosen, so guess you need to build in a loop.

 

[RuralGuy]

I'm thinking the Split() function might work well for the function you need.

 

[David Anderson]

Ah! just noticed that you want to vary the number of words chosen, so guess you need to build in a loop.

Hi Brian,
Yes, as per my OP, I need my function to take two arguments - the string of words and the number of words to be selected.

David

 

[Brianwarnock]

RG maybe right but I'm rusty on arrays so how about

Function nwords(astring As String, n As Long) As String
Dim L%, x%
L = 0
For x = 1 To n
L = InStr(L + 1, astring, " ")
Next x
nwords = Left(astring, L - 1)

End Function

Again no checking but got to leave you something todo.

Brian

 

[vbaInet]

Here's an extract from the help files re Sel Text property:

TextBox.SelText Property

Show All​

Hide All​

The SelText property returns a string containing the selected text. Read/write String. expression.SelText
expression A variable that represents a TextBox object.
Remarks

If no text is selected, the SelText property contains a Null value.
The SelText property uses a string expression that contains the text selected in the control. If the control contains selected text when this property is set, the selected text is replaced by the new SelText setting.
To set or return this property for a control, the control must have the focus . To move the focus to a control, use the SetFocus method.
Example

The following example uses two event procedures to search for text specified by a user. The text to search is set in the form's Load event procedure. The Click event procedure for the Find button (which the user clicks to start the search) prompts the user for the text to search for and selects the text in the text box if the search is successful.
Visual Basic for Applications

Sub Form_Load()
    Dim ctlTextToSearch As Control
    Set ctlTextToSearch = Forms!Form1!TextBox1
    ctlTextToSearch.SetFocus      ' SetFocus to text box.
    ctlTextToSearch.[B]SelText[/B] = "This company places large orders " _
        & "twice a year for garlic, oregano, chilies and cumin."
End Sub

Sub Find_Click()
    Dim strSearch As String, intWhere As Integer
    Dim ctlTextToSearch As Control
    ' Get search string from user.
    With Me!Textbox1
        strSearch = InputBox("Enter text to find:")
        ' Find string in text.
        intWhere = InStr(.Value, strSearch)
        If intWhere Then
            ' If found.
            .SetFocus
            .SelStart = intWhere - 1
            .SelLength = Len(strSearch)
        Else
            ' Notify user.
            MsgBox "String not found."
        End If
    End With
End Sub

 

[David Anderson]

I'm thinking the Split() function might work well for the function you need.

Hi Allan,
As it happens, I obtained a VB.NET solution for exactly this same requirement on an ASP.NET website. That solution uses the Split function but my initial attempts to convert the function to VBA didn't work.

In case someone else is better at converting VB.NET to VBA, here is the VB.NET function that does exactly what I want,

Public Shared Function SelectWords(ByVal text As String, ByVal maxWords As Integer) As String

'Code from Mike Banavige. Ref http://forums.asp.net/p/1492719/3513121.aspx#3513121
IfString.IsNullOrEmpty(text) Then Return String.Empty
If maxWords <= 0 Then Return String.Empty
'When using a blank space as a word boundary, if there are multiple
'spaces between words in the text string then the Split method will treat
'the extra blanks as being separate words. However, the
'StringSplitOptions.RemoveEmptyEntries overload ensures the omission
'of such blanks from the return value.
Dim splitchars As Char() = {" "c}
Dim words As String() = text.Split(splitchars, StringSplitOptions.RemoveEmptyEntries)
Return (String.Join(" ", words, 0, Math.Min(maxWords, words.Length)))
​

End Function

 

[vbaInet]

This is what I came up with. Not sure if it works, untested but compiles.

Public Function SelectWords(ByVal textVal As String, ByVal maxWords As Integer) As String
    Dim splitWords() As String, i As Long, returnMax As Long

    If textVal & "" = "" Then
        SelectWords = ""
    Else
        If maxWords <= 0 Then
            SelectWords = textVal
        Else
            splitWords = Split(textVal, " ")
            returnMax = IIf(UBound(splitWords) >= maxWords, maxWords - 1, UBound(splitWords))
            
            For i = 0 To returnMax
                SelectWords = SelectWords & " " & splitWords(i)
            Next
            SelectWords = Trim(SelectWords)
        End If
    End If
End Function

Did you try Brian's solution? It looks like what you're looking for.

 

[RuralGuy]

I think your standard module function will look something like:

Option Compare Database
Option Explicit

Function SelectWords(InString As String, InCount As Integer) As String
'-- Return a string with InCount words from the InString supplied separated by one space as a delimiter
   Dim MyArray() As String
   Dim x As Integer

   SelectWords = ""                       '-- Initialize the return value
   If Len(InString) > 0 Then
      MyArray = Split(InString, " ")
      If UBound(MyArray) > 0 Then      '-- We have some array elements
         For x = 0 To UBound(MyArray) - 1
            SelectWords = Trim(MyArray(x))
            '-- Add a trailing space as a delimiter
            SelectWords = SelectWords & " "
         Next
         '-- Strip off the last space
         SelectWords = left(SelectWords, Len(SelectWords) - 1)
      End If
   End If

End Function

 

[vbaInet]

RG maybe right but I'm rusty on arrays so how about

Function nwords(astring As String, n As Long) As String
Dim [COLOR=Red]L%[/COLOR], [COLOR=Red]x%[/COLOR]
L = 0
For x = 1 To n
L = InStr(L + 1, astring, " ")
Next x
nwords = Left(astring, L - 1)

End Function

Again no checking but got to leave you something todo.

Brian

Brian, what are those postfixes you've used in your variable declaration?

 

[David Anderson]

Thanks a bundle for all the code suggestions, guys. I'm a bit tied up with something else at the moment but I hope to give them a trial run later this evening.

Your help is much appreciated.

David

 

[vbaInet]

Looks like I and RuralGuy had the same thinking cap on hehe!

 

[Brianwarnock]

Brian, what are those postfixes you've used in your variable declaration?

Short hand for defining integers, came across it in here somewhere.

Brian

Just looked on Help

Integer data type
A data type that holds integer variables stored as 2-byte whole numbers in the range -32,768 to 32,767. The Integer data type is also used to represent enumerated values. The percent sign (%) type-declaration character represents an Integer in Visual Basic.

Edit & for Long, think i'll check them all out, should have noticed this years ago.

 

[Brianwarnock]

Couldn't let the array solution lie so after some trial and error here is my effort.

Brian

Function mysplit(astring As String, n As Long) As String
Dim pos%, x%
Dim myarray() as String
Do
pos = InStr(astring, "  ")
astring = Replace(astring, "  ", " ")
Loop Until pos = 0
myarray = Split(astring, " ")
If n > UBound(myarray) Then n = UBound(myarray) + 1

For x = 0 To n - 1
mysplit = mysplit & " " & myarray(x)
Next x
mysplit = Ltrim(mysplit)
End Function

 

[vbaInet]

I was thinking it was some form of casting but I thought I should confirm. Thanks.

 

[Brianwarnock]

Vba I have added to the post, its all inhelp, its just finding the info in that crazy useless system.

Brian

 

[vbaInet]

Thanks Brian. It's not the sort of things you look for in the help files so that's why we missed it

Some don't have an abbreviation though, like Boolean and Date data types (for example). For readability I will stick with spelling it out, however, it would come in handy in other cases.