udf to extract 2 words at a time

[LFKim2018]

I found a UDF that extracts a word at a time, how do I change the code to extract 2 words at a time?
many many thanks

Function findword(Source As String, Position As Integer)
    Dim arr() As String
    arr = VBA.Split(Source, " ")
    xCount = UBound(arr)
    If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
        findword = ""
    Else
        findword = arr(Position - 1)
    End If
End Function

 

[Peter_SSs]

Instead of giving us some code that doesn't do what you want, could you give us some sample data and the expected result, together with explanation in relation to that sample data?

 

[LFKim2018]

Mr. Peter_SSs
sorry about that..

say I have a string like: "one two three four" in column A
in column B > the result of =findword(A1,1) would be "one"
in column C > the result of =findword(A1,2) would be "two"
.. and so on
now, How do I get 2 words at a time, like "one two" then "three four" so on...
and it would be nice to be applicable for any delimiter aside from space - say comma,slash,colon etc..
many thanks

 

[Rick Rothstein]

You did not say what you want returned if there are an odd number of words and you asked for that odd word or if you specified a word group that did not exist. I assumed the empty string would be returned for those conditions. Here is a UDF (user defined function) that will do what you asked for...

[table="width: 500"]
[tr]
	[td]Function TwoWords(ByVal Txt As String, WhichTwo As Long)
  Dim X As Long, Words() As String
  For X = 1 To Len(Txt)
    If Mid(Txt, X, 1) Like "[!A-Za-z0-9]" Then Mid(Txt, X) = " "
  Next
  Words = Split(Application.Trim(Txt))
  On Error GoTo NoMoreWords
  TwoWords = Words(2 * WhichTwo - 2) & " " & Words(2 * WhichTwo - 1)
  Exit Function
NoMoreWords:
  TwoWords = ""
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TwoWords just like it was a built-in Excel function. For example, to retrieve words 5 and 6, you would ask for the 3rd group like this...

=TwoWords(A1,3)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[LFKim2018]

Mr. Rothstein
like a charm!
thank you very much

 

[Peter_SSs]

and it would be nice to be applicable for any delimiter aside from space - say comma,slash,colon etc..

Here is another pretty short UDF that permits this** and also gives you the choice of how many "words" to extract each time.

** There may be some delimiters that fail with my code due to them being "special characters" within regular expressions. It would be possible to allow those delimiters too, but more difficult to implement so I haven't tried to do that since I took your enquiry to just be a general one for interest.

Each formula below is copied across to column E.
Row 1 is to extract the default 2 words with space delimiter
Row 3 extracts groups of 3 words with the default space delimiter
Row 5 extracts 2 words but with comma delimiter
Row 7 extracts 4 "words" with "z" delimiter
Row 9 extracts single words with the 3-character " / " delimiter.

Function GetWords(s As String, WhichLot As Long, Optional Num As Long = 2, Optional Delim As String = " ") As String
  With CreateObject("VBScript.Regexp")
    .Global = True
    .Pattern = Replace(Replace("([^#]+#){%}([^#]+)(?=#|$)", "#", Delim), "%", Num - 1)
    If WhichLot <= .Execute(s).Count Then GetWords = .Execute(s)(WhichLot - 1)
  End With
End Function

Excel Workbook
	A	B	C	D	E
1	one two three four five six seven	one two	three four	five six
2
3	one two three four five six seven	one two three	four five six
4
5	one,two,three,four,five	one,two	three,four
6
7	abczdefzghijzwwwazbbbbzcc	abczdefzghijzwwwa
8
9	one / two / three / four	one	two	three	four
Sheet1

 

[LFKim2018]

Here is another pretty short UDF that permits this** and also gives you the choice of how many "words" to extract each time.

Mr. Peter_SSs
your dedication to help is beyond words!
Thank you very very much..

 

[Peter_SSs]

You're welcome. Thank you you for your kind words.