WOW - that worked (the UDF) beautifully. THANK YOU.
I'm going to use it, but I've got the issue that the (#&$)(@*&# list has names like this:
Joan Major and Jack Major
Sally Brown
Bill Smith and Susan Smith
Jean Baker and Bob Baker
Elizabeth Thompson
It is shaving off the last names of those in the cells that only have one person in them. Still - this helps so much - THANK YOU!!!!
Joan
Quote:
Originally Posted by Claus Busch
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
|