Last Name, First Name to First Name Last Name (with a twist)

[Bruzer]

Most of customer list comes in as:

Doe, John

or

Doe, John & Jane

It easy to change those to:

John Doe

or

John & Jane Doe

by using:

=TRIM(MID(A2&" "&A2,FIND(",",A2)+1,LEN(A2)))

However, we also have customers entered as:

Doe, John & Smith, Jane

and we'd like to have it appear as:

John Doe & Jane Smith

Is there a formula that could handle that, or does it require a VBA solution?

Thanks.

 

[Biz]

VBA RegScript would be ideal solution but unfortunately I'm still learning it.
I'm sure someone in the forum knows how to answer this question RegScript.

 

[Bruzer]

OK, here's what I came up with. The first function handles the "normal" case, the second handles the special case. This was based on VBA code from here. It's not pretty, but it works!

[COLOR=#222222][FONT=Verdana]' Handles normal case of "lastname, firstname" or "lastname, firstname1 & firstname2"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Function FlipNames(MyString As Variant)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim lastname As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim firstname As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If InStr(1, MyString, ",") Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     lastname = Trim(Left(MyString, WorksheetFunction.Find(",", MyString) - 1))[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     firstname = Trim(Right(MyString, Len(MyString) - WorksheetFunction.Find(",", MyString) - 1))[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]FlipNames = Trim(firstname & " " & lastname)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Function[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]' Handles special case of "lastname1, firstname1 & lastname2, firstname2"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Function FlipNames2(MyString As Variant)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim name1 As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim name2 As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim lastname1 As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim firstname1 As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim lastname2 As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim firstname2 As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If InStr(1, MyString, "&") Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     name1 = Left(MyString, WorksheetFunction.Find("&", MyString) - 1)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     name2 = Right(MyString, Len(MyString) - WorksheetFunction.Find("&", MyString) - 1)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If InStr(1, name1, ",") Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     lastname1 = Trim(Left(name1, WorksheetFunction.Find(",", name1) - 1))[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     firstname1 = Trim(Right(name1, Len(name1) - WorksheetFunction.Find(",", name1) - 1))[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If InStr(1, name2, ",") Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     lastname2 = Trim(Left(name2, WorksheetFunction.Find(",", name2) - 1))[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]     firstname2 = Trim(Right(name2, Len(name2) - WorksheetFunction.Find(",", name2) - 1))[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]FlipNames2 = Trim(firstname1 & " " & lastname1 & " & " & firstname2 & " " & lastname2)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Function[/FONT][/COLOR]