I'm having problem removing duplicate surnames in a single cell.
Example:

Joan Major and Jack Major

What I have found so far comes back as:

Joan Major and Jack

When what I need is:

Joan and Jack Major

I can't get it to delete the first instance - is that even possible????
Thanks. I'm going nuts and feeling lame.

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

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

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

Hi Joan,

Am Fri, 26 May 2017 18:18:39 +0100 schrieb JoannieMaj:

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!!!!

download the test file from here (macros are disabled in OneDrive):
https://1drv.ms/x/s!AqMiGBK2qniTgZN1558VWDDLeTHzmw


Regards
Claus B.
--
Windows10
Office 2016

Hi Joan,

Am Fri, 26 May 2017 18:18:39 +0100 schrieb JoannieMaj:

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!!!!

download the test file from here (macros are disabled in OneDrive):
https://1drv.ms/x/s!AqMiGBK2qniTgZN1558VWDDLeTHzmw


Regards
Claus B.

Suggestion:
Search your array for dupes and if found build a new value...

Dim vNames, vTmp()
vNames = Split(myRange, " ")

If vNames(1) = vNames(UBound(vNames)) Then
Redim vTmp(Ubound(vNames) - 1)
vTmp(0) = vNames(0): vTmp(1) = vNames(2)
vTmp(2) = vNames(3): vTmp(4) = vNames(5)
End If

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Didn't think the rest was necessary but...

Function myNames$(myRange As Range)
Dim vNames, vTmp()
vNames = Split(myRange, " ")

If vNames(1) = vNames(UBound(vNames)) Then
Redim vTmp(Ubound(vNames) - 1)
vTmp(0) = vNames(0): vTmp(1) = vNames(2)
vTmp(2) = vNames(3): vTmp(4) = vNames(5)
End If
myNames = Join(vTmp, " ")
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

CLAUS YOU ARE MY HERO!!!!!

I have no human understanding of how you do what you do, but you are a WIZARD! Thanks so much - I've got a list of almost 12K names that would have taken me a lifetime to redo - and you just whipped it right out of your head.

THANK YOU THANK YOU THANK YOU!!!!

Actually tested, but still not as brilliant as Claus' wizardry!

Function myNames$(myRange As Range)
Dim vNames, vTmp(), s1$
s1 = myRange.Value

If vNames(1) = vNames(UBound(vNames)) _
And UBound(vNames) 1 Then
vNames = Split(s1, " "): ReDim vTmp(UBound(vNames) - 1)
vTmp(0) = vNames(0): vTmp(1) = vNames(2)
vTmp(2) = vNames(3): vTmp(3) = vNames(4)
s1 = Join(vTmp, " ")
End If
myNames = s1
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I sleep better at night knowing there are brains like this on the planet. :)
THANKS!
Joan

I sleep better at night knowing there are brains like this on the
planet. :)
THANKS!

We're always glad to help where/when we can! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion