Extracting First and Last Words

To all interested
Here is my macro to split names. It assumes that the names will be split accross three columns and the middle name or names are placed into the second column
Sub SplitNames()
Dim c As Range, MidNames As String
Dim x As Variant, i As Integer, n As Integer

For Each c In Selection
If Len(c) > 0 Then
x = Split(c, " ")
n = UBound(x) + 1
c.Offset(0, 1) = x(LBound(x))
c.Offset(0, 3) = x(UBound(x))

If n = 3 Then
MidNames = x(LBound(x) + 1)
c.Offset(0, 2) = MidNames
ElseIf n > 3 Then
For i = 1 To UBound(x) - 1
MidNames = MidNames & x(i) & " "
Next i
c.Offset(0, 2) = Trim(MidNames)
End If
MidNames = ""
End If
Next c

End Sub

Names such as du Pre have been entered du& Hard Space & Pre where hard space is entered as ALT + 0160
[{fig]}

@Luis Pazeto,

I know this is a few years late, but there is a more compact formula available to find the last word in a piece of text...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

The above assumes the text will have less than 99 characters in it... if the text could be longer, just replace the two 99's with a number guaranteed to be larger than length of the text plus maybe an additional 10% as a cushion.

Brilliant!

I used to apply this formula to get the last word in a string:

=RIGHT(REPLACE(A10," ","#",LEN(A10)-LEN(REPLACE(A10," ",""))),LEN(REPLACE(A10," ","#",LEN(A10)-LEN(REPLACE(A10," ",""))))-FIND("#",REPLACE(A10," ","#",LEN(A10)-LEN(REPLACE(A10," ","")))))

But the formula shared by Allan is much more simple!

@williamwclee

The number used can be any higher than the length of last word. But as this length can vary for word to word, 255 was used because a word hardly exceed this limit.

Sorry for my poor english.

why 255 characters?
is it a max of characters that a cell can hold?

When I read this tip I first thought why not combine the FirstWord and LastWord macros by adding a second argument to indicate what you want. Then it came up to me that the second argument could be used to indicate not only the first or last but any word in the cell. Here it is:

Function GetWord(c As String, Optional x As Integer = 1)
Dim arr
arr = Split(Trim(c))
x = x - 1
If x = -1 Or x > UBound(arr) Then x = UBound(arr)
GetWord = arr(x)
End Function

You use GetWord like this (suposing A1 contains "first second last", without the quotes:
=GetWord(A1) --> first (if you omit the 2nd argument the default is 1)
=GetWord(A1,0) --> last
=GetWord(A1,1) --> first
=GetWord(A1,2) --> second
=GetWord(A1,3) --> last
=GetWord(A1,9) --> last
If the number of the 2nd argument is > than the number of words in the cell the last word is returned.