Hi Joannie,

Am Thu, 25 May 2017 02:15:53 +0100 schrieb JoannieMaj:

Joan Major and Jack Major

When what I need is:

Joan and Jack Major

try:
=SUBSTITUTE(A1,MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99),,1)

Or do it with an UDF:

Function myNames(myRange As Range) As String
Dim varNames As Variant

varNames = Split(myRange, " ")
myNames = Application.Substitute(myRange, varNames(UBound(varNames)), "", 1)
End Function

and call that function into the sheet with e.g.:
=myNames(A1)

Regards
Claus B.
--
Windows10
Office 2016