Changing/Concatenate Names from [FIRST NAME/LAST NAME] to [LAST/FI

[Guest]

Hello All -

Slightly problematic - there are both individuals and entities in my name
range. The name range is First Name, Last Name, and needs to be changed to
Last Name, First.

I've got certain KEY words which I am thinking of using to differentiate
individuals from entities (trusts, incorporations, companies, etc.). These
would be:

Trust, Inc., Corp., #and#, Joint, Fund, L.P., LLP, LLC, PC, Tenant, P.A.,
Gmbh.

Here's a sample list (single column):

The Buscht Family Trust
Andrew Barry
Andrea Caracoi
Jacques Dore
Frederick N. Kahn and Delilah H. Kahn, Joint Tenants WROS
Harold Gorst
Matthew V. Hahnd
Jacob Hassan
Wayne Hendricksen and Leila T. Hendricksen
Barrey Hespa
R.N. Karta
Roseanne Lee-Hong
Mark L. Savage and Kristy M. Savage as Trustees of the Savage Family
Revocable Trust U/A 5/29/03
Sal Zarraba and Theresa Zarraba Family Trust, U.T.D. 09/12/02
Sal Zarraba and Theresa Zarraba Family Trust, U.T.D. 8/18/03
Sal Zarraba and Theresa Zarraba Family Trust, U.T.D. May 12, 2002
JR Tozrea
Tony Van Darlen
W. Todd Whipkey and Samantha R. Whipkey as Community Property
Paul R. Young


I'm thinking of using the FIND formula for each cell of the Name range,
which looks for the above KEY words, and if it fails to find such words, it
treats the name as an Individual, then rearranges those cells into the
desired LAST Name, FIRST Name format.

Any suggestions for an elegant way to do this?

thanks!

 

[Guest]

Wow - tricky! Good luck! But I think you can do what you are saying without
too much trouble as long as you realize it will not give guaranteed results.

I would suggest a user function, e.g. LooksLikePerson(ListName) that returns
a boolean - true if the value you pass to it has the characteristics of a
person's name, false if it has any of your flags for groups/trusts, etc. And
in VBA you can use the InStr function.

Would look something like this:

Public Function LooksLikePerson(ListName As String) As Boolean

Dim Result As Boolean

Result = True

Result = (Result And (InStr(ListName, "Trust") = 0))
Result = (Result And (InStr(ListName, "Inc") = 0))
' similar for each word you are looking for.
' If any of the words is in the name, the second part of the "And" is FALSE.
' By using Result = Result And... you are making sure that once Result is
false it stays false, even if the next test is true

LooksLikePerson = Result
End Function