[code]I would like to get the below cod fixed in a new excel file. Is it possible to get this code preset in excel, that way I don't have to enter this code into each module when I want to use it?
Extract nth word from text string with User Defined Function
If you want to extract the second, third or any nth word from the text string, you can create a user defined function to deal with it.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module window.
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
3. Then save the code, in this example, I will get the third word from the string, so type this formula =findword(A2,3) into a blank cell B2, and then drag this cell's AutoFill handle to the range as you need. See screenshot:
Note: In the above formula, A2 is the cell where you want to extract word from, the number 3 indicates the third word in the string that you will extract, and you can change them as you need.
[code]
Bookmarks