Replace "FIRSTNAME LASTNAME" with "LAST"

[chrishutson123]

Hello:

I have a cell with the string "FIRSTNAME LASTNAME". I would like to replace
it with "LAST". I have some special cases:

"MERRILL C CURRIER" should yield "CURR"
"JON SUN" should yield "SUN "
"JON C SUN" should yield "SUN "
"TRAVIS SMITH" should yield "SMIT"

Can anyone help? I am very appreciative. You should know that I am new to
VBA in Excel. Thanks.

Chris.

 

[Gary''s Student]

Without VBA:

In B1, enter:
=SEARCH("|",SUBSTITUTE(A1," ","|",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))+1


In C1, enter:
=MID(A1,B1,4)

With VBA:

Sub lastname()
s = Split(ActiveCell.Value, " ")
u = UBound(s)
MsgBox (Left(s(u), 4))
End Sub

 

[Don Guillett]

try
Sub truncatenames()
For Each c In Range("e2:e25")
x = InStrRev(c, " ") + 1
'MsgBox x
If (Len(c) - x) < 4 Then
c.Value = Right(c, Len(c) - x + 1)
Else
c.Value = Mid(c, x, 4)
End If
Next c
End Sub

 

[chrishutson123]

Thank you so much!!! That worked like a champ. I have another question:

I have a cell with account numbers formatted like "1234-2" or "12345-2" or
"123456-2". The main part of the account number could be as few as 3
characters and as many as 6 characters followed by a "-2". I need to convert
this field to a 40 character string with leading zeroes. Can you help?

 

[Don Guillett]

Custom format
000000-2

 

[Rick Rothstein \(MVP - VB\)]

With VBA:

Sub lastname()
s = Split(ActiveCell.Value, " ")
u = UBound(s)
MsgBox (Left(s(u), 4))
End Sub

Another way...

MsgBox Left(Mid(ActiveCell.Value, InStrRev(ActiveCell.Value, " ") + 1), 4)

Rick