Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing/Concatenate Names from [FIRST NAME/LAST NAME] to [LAST/FI
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! -- Some-time novice VBA for Excel programmer/office mikey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing/Concatenate Names from [FIRST NAME/LAST NAME] to [LAST/FI
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 "BrownRadagast" wrote: 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! -- Some-time novice VBA for Excel programmer/office mikey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Defined Names | Excel Worksheet Functions | |||
Concatenate first, middle and last names | Excel Discussion (Misc queries) | |||
Changing mulitple tab names | Excel Worksheet Functions | |||
Changing worksheet names | Excel Worksheet Functions | |||
changing 'names' | Excel Programming |