VBA code to extract strings from a single column to another in a reverse way

[Nravota]

Hy guys,

I am very new to visual basic. I am currently using a vba function to extract words from a single column. For example, I have the names "Julia loves to ride a bike" all in cell A1, and "The wether is sunny" in cell A2 and I am able to extract the second words in column B when I type in excel =findword(A1,2)

Julia loves to ride a bike	loves
The wether is sunny	wether

<TBODY>
</TBODY>

However, I would like to extract the second words from backwards, in this case "a" and "is". Is there a way to reverse this function? I want to use the very same function but to make it count 1,2,3 etc. from right to left. So it would look like:

Julia loves to ride a bike	a
The wether is sunny	is

<TBODY>
</TBODY>

The code is:

Function FindWord(Source As String, Position As Integer)
'Update 20131202
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


Is there way to do that? Many thanks in advance!

 

[Rick Rothstein]

Below is the function you posted (FindWord) written a little more compactly as well as the function you requested (which I called FindWordRev)...

Function FindWord(Source As String, Position As Integer) As String
  On Error Resume Next
  FindWord = Split(Source)(Position - 1)
  On Error GoTo 0
End Function

Function FindWordRev(Source As String, Position As Integer) As String
  Dim Arr() As String
  Arr = Split(Source)
  On Error Resume Next
  FindWordRev = Arr(UBound(Arr) - Position + 1)
  On Error GoTo 0
End Function

In case you are interested, both of these functions can be accomplished directly with worksheet formulas, see the following articles from my mini-blog...

Get Field from Delimited Text String

Get "Reversed" Field from Delimited Text String

 

[Nravota]

Hi Rick, this is a great function, I could not find this anywhere on the net. Thank you so much!