Hi again,

Am Thu, 25 May 2017 08:06:45 +0200 schrieb Claus Busch:

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

sorry, forgot to delete the space behind the name:
=TRIM(SUBSTITUTE(A1,MID(A1,FIND("#",SUBSTITUTE(A1, " ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99),,1))

or:

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

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


Regards
Claus B.
--
Windows10
Office 2016