Hi,

How can I find the last word in a cell containing "Firstname Initial
Lastname"? I have tried the Mid and Left functions with a for next loop
but I think I need to work backwards to find the space.

Any suggestiongs please.

Andrew


--
aph
------------------------------------------------------------------------
aph's Profile: http://www.excelforum.com/member.php...o&userid=17175
View this thread: http://www.excelforum.com/showthread...hreadid=476748

aph wrote:

Hi,

How can I find the last word in a cell containing "Firstname
InitialLastname"? I have tried the Mid and Left functions with a for next
loopbut I think I need to work backwards to find the space.

Any suggestiongs please.

Use the Mid & Left functions to separate the firstname from the
remainder, then do it again on the remainder to separate the initial
from the lastname.


--
Gordon Rainsford

This UDF returns the lastname (use space as Separchar):

Function Lastword(InputString As String, SeparChar As String) As String
Dim i As Long
Dim ByteStr, ResultStr As String
ResultStr = ""
For i = Len(InputString) To 1 Step -1
ByteStr = Mid(InputString, i, 1)
If ByteStr = SeparChar Then
ResultStr = Right(InputString, Len(InputString) - i)
Exit For
End If
Next i
Lastword = ResultStr
End Function

Regards,
Stefi


"Gordon Rainsford" wrote:

aph wrote:

Hi,

How can I find the last word in a cell containing "Firstname
InitialLastname"? I have tried the Mid and Left functions with a for next
loopbut I think I need to work backwards to find the space.

Any suggestiongs please.

Use the Mid & Left functions to separate the firstname from the
remainder, then do it again on the remainder to separate the initial
from the lastname.


--
Gordon Rainsford

Public Function FindName(ByVal MyName As String)

FindName = Right(MyName, Len(MyName) - InStrRev(MyName, " "))

End Function

Example of use:
MyName = "Kenneth Graham Dales"
LastName = FindName(MyName)
? LastName
Dales
MyName = Trim(Replace(MyName, LastName, ""))
? MyName
Kenneth Graham
MiddleName = FindName(MyName)
? MiddleName
Graham
FirstName = Trim(Replace(MyName, MiddleName, ""))
? FirstName
Kenneth

--
- K Dales


"aph" wrote:


Hi,

How can I find the last word in a cell containing "Firstname Initial
Lastname"? I have tried the Mid and Left functions with a for next loop
but I think I need to work backwards to find the space.

Any suggestiongs please.

Andrew


--
aph
------------------------------------------------------------------------
aph's Profile: http://www.excelforum.com/member.php...o&userid=17175
View this thread: http://www.excelforum.com/showthread...hreadid=476748

Kenneth

Works perfectly - thanks (I am sure the others do as well but I do like
a 1 line !)

Andrew


K Dales Wrote:
Public Function FindName(ByVal MyName As String)

FindName = Right(MyName, Len(MyName) - InStrRev(MyName, " "))

End Function

Example of use:
MyName = "Kenneth Graham Dales"
LastName = FindName(MyName)
? LastName
Dales
MyName = Trim(Replace(MyName, LastName, ""))
? MyName
Kenneth Graham
MiddleName = FindName(MyName)
? MiddleName
Graham
FirstName = Trim(Replace(MyName, MiddleName, ""))
? FirstName
Kenneth

--
- K Dales


"aph" wrote:


Hi,

How can I find the last word in a cell containing "Firstname Initial
Lastname"? I have tried the Mid and Left functions with a for next
loop
but I think I need to work backwards to find the space.

Any suggestiongs please.

Andrew


--
aph

------------------------------------------------------------------------
aph's Profile:
http://www.excelforum.com/member.php...o&userid=17175
View this thread:
http://www.excelforum.com/showthread...hreadid=476748




--
aph
------------------------------------------------------------------------
aph's Profile: http://www.excelforum.com/member.php...o&userid=17175
View this thread: http://www.excelforum.com/showthread...hreadid=476748

Try the SPLIT() function. It is similar to Text to Columns in the worksheet.
--
Gary''s Student


"aph" wrote:


Hi,

How can I find the last word in a cell containing "Firstname Initial
Lastname"? I have tried the Mid and Left functions with a for next loop
but I think I need to work backwards to find the space.

Any suggestiongs please.

Andrew


--
aph
------------------------------------------------------------------------
aph's Profile: http://www.excelforum.com/member.php...o&userid=17175
View this thread: http://www.excelforum.com/showthread...hreadid=476748