Swap first and last name dropping middle initial

**sparky101747** · 02-06-2011, 07:55 PM

I have a list of names that are listed as last name, first name, and sometimes middle initial as in:

D2:Dn

DUNKIN STEVAN R & GINA R
LOPEZ ANTHONY
ZHANG SHENGWEI
OVALLE NELLY E
PARK CASEY H & CHRISTINE
LOINAB AHMAD Z & WAZHMA
MCCLANE CHERYL & RALPH TRUST
WALKER WILLIAM G JR & MARIE B
SONG MELVIN & DOREEN K Y

I would like to know how to swap first and last names dropping middle initial but keeping &
as in:

E2:En

Stevan & Gina Dunkin
Anthony Lopez
Shengwei Zhang
Nelly Ovalle
Casey & Christine Park
Ahmad & Wazhma Loinab
Mcclane Trust
William & Marie Walker
Melvin & Doreen Song

Note: the names are intially all caps and need to proper, and i have exemption words in column A to not change things like "trust" which i can minipulate manually because there aren't that many of them.

I know I am asking a lot but I have over 1000 names per week and am trying to streamline this process. Thanks in advance

**DonkeyOte** · 02-07-2011, 04:12 AM

I think you will (realistically) be looking at VBA for this - the variations will make formula based approaches unwieldy

Attached is a file with a couple of possibilities - none of which are great but they may suffice.
(hopefully someone can come up with a more efficient & capable Regular Expression pattern)

Notes:
-- column B - I added titles to be excluded - eg Jr/Sr to operate per Column A.

-- the formula approach is not meant as anything other than a "what you could do with formulae" -> it doesn't generate expected results.

edit: macros must be enabled given use of VBA

**sparky101747** · 02-07-2011, 11:40 AM

Thank you for your time, DonkeyOte, this actually worked better than I ever expected!

**sparky101747** · 02-13-2011, 09:48 PM

I know when I wrote out the example I had changed the expected result for MCCLANE CHERYL & RALPH TRUST to Mcclane Trust, but it wasn't supposed to be. If someone could change what DonkeyOte has done to not do anything with cells containing exemption words except copy over to new cell so the result in this example would be MCCLANE CHERYL & RALPH TRUST.

Thanks for looking.

**DonkeyOte** · 02-14-2011, 04:43 AM

I am presuming you're referring to the last file I posted... simply modify the formula (the UDF is unchanged)

Please Login or Register  to view this content.

In the above we simply say - if a keyword (A1:A5) is found in string (D2) return the string (D2) else apply the UDF

edit: if using the formula/UDF in F2 (rather than G) then you would need to modify that UDF slightly such that:

Please Login or Register  to view this content.

becomes:

Please Login or Register  to view this content.

**sparky101747** · 02-14-2011, 11:41 AM

That Did it. Thanks again

Swap first and last name dropping middle initial

LinkBack

Thread Tools

Rate This Thread

Display

Swap first and last name dropping middle initial

Re: Swap first and last name dropping middle initial

Re: Swap first and last name dropping middle initial

Re: Swap first and last name dropping middle initial

Re: Swap first and last name dropping middle initial

Re: Swap first and last name dropping middle initial

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions