Reversing the position of people's names in a cell

[Colin Hayes]

Hi

I have a small problem.

I have a list of names in column A. All are in Surname , Christian Name
format.

I need to be able to reverse these so that

Smith John

becomes

John Smith.

It only needs to apply where there are two words in a cell. Some cells
have 3 or more words - it wouldn't apply to them at all.

Can anyone help with this?


Best Wishes

 

[Don Guillett Excel MVP]

Sub movenamesifTWO()
For Each c In ActiveSheet.Range("f1:f4")
If Len(c) <> 0 And Len(c) - _
Len(Application.Substitute(c, " ", "")) + 1 = 2 Then
p1 = InStr(c, " ")
c.Value = Mid(c, p1 + 1, 256) & " " & Left(c, p1)
End If
Next c

 

[Colin Hayes]

Sub movenamesifTWO()
For Each c In ActiveSheet.Range("f1:f4")
If Len(c) <> 0 And Len(c) - _
Len(Application.Substitute(c, " ", "")) + 1 = 2 Then
p1 = InStr(c, " ")
c.Value = Mid(c, p1 + 1, 256) & " " & Left(c, p1)
End If
Next c

Hi Don

OK thanks for getting back.

I couldn't get this going , I'm afraid. Maybe the code is wrapped or
corrupted somehow , but I wasn't able to run without Excel giving
errors. I'll keep trying though.



Best Wishes

 

[Ron Rosenfeld]

Hi

I have a small problem.

I have a list of names in column A. All are in Surname , Christian Name
format.

I need to be able to reverse these so that

Smith John

becomes

John Smith.

It only needs to apply where there are two words in a cell. Some cells
have 3 or more words - it wouldn't apply to them at all.

Can anyone help with this?


Best Wishes

From your examples, it appears as if the words are separated by either
a <space> or a <comma><space>.

That being the case, you can use this formula:

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))<>1,
A1,TRIM(MID(TRIM(A1),FIND(" ",A1),255)) & " "& LEFT(
TRIM(A1),MIN(FIND({" ",","},A1&","))-1))

 

[Colin Hayes]

From your examples, it appears as if the words are separated by either a <space>
or a
<comma><space>.

That being the case, you can use this formula:

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))<>1,
A1,TRIM(MID(TRIM(A1),FIND("
",A1),255)) & " "& LEFT( TRIM(A1),MIN(FIND({" ",","},A1&","))-1))

Thank you for this formula. The names I have listed are separated by
spaces , yes.

I did apply the formula , and it gave #VALUE! Errors for the target
names containing two elements. It's just names with two elements
separated with a comma that I need to invert.

Other entries with one , three or more elements were treated correctly
by the formula , and left with no change.

 

[Don Guillett Excel MVP]

"If desired, send your file to dguillett@gmail,com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

Thank you for this formula. The names I have listed are separated by
spaces , yes.

I did apply the formula , and it gave #VALUE! Errors for the target
names containing two elements. It's just names with two elements
separated with a comma that I need to invert.

Other entries with one , three or more elements were treated correctly
by the formula , and left with no change.

Either you made a typo when you copied my formula, or your newsreader
added some additional characters or spaces, or your names are not
entered in the ways you have described.

Between your first post, and this one, you have described multiple
ways in which the first name and last name might be separated:

====================================
Surname , Christian Name
<LastName><space><comma><space><FirstName>
-------------------------------------
Smith John
<LastName><space><FirstName>
---------------------------------------
The names I have listed are separated by
spaces
--------------------------------------
It's just names with two elements
separated with a comma that I need to invert.
==============================

The only one of those formats that my formula will not handle, is the
last, where you write that the names are separated only by a comma (no
spaces), but, in that case, it will not give a VALUE error; it will
only return the string unchanged.

Please copy and paste (don't type) the cell entry that results in the
VALUE error; and also copy and paste the formula as you have it on
your worksheet for that cell.

Or the following User Defined Function should handle all of those
different varieties:

To enter this User Defined Function (UDF), <alt-F11> opens the Visual
Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like
                   =RevLastFirst(A1)
 in some cell.

======================================
Option Explicit
Function RevLastFirst(s As String) As String
   Dim re As Object, mc As Object
   Const sPat As String = "^(\b\S+\b)[^-\w]+(\b\S+\b)$"
   Dim sTrimmed As String
sTrimmed = Trim(s)
Set re = CreateObject("vbscript.regexp")
    re.Pattern = sPat
    re.Global = True
RevLastFirst = re.Replace(sTrimmed, "$2 $1")
End Function
==========================- Hide quoted text -

- Show quoted text -

 

[Colin Hayes]

Please copy and paste (don't type) the cell entry that results in the VALUE error;
and also copy and
paste the formula as you have it on your worksheet for that cell.

Hi Ron

(a) OK I've pasted below sample cell entries that give VALUE errors :

Artist
Browne Jackson
Ellington Duke
Fields Gracie
H P Zinker
Oconnor Hazel
Ifield Frank
Memphis Slim
Miller Glenn
New Riders Purple Sage

This is the formula from B1 :

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))<>1,
A1,TRIM(MID(TRIM(A1),FIND("
",A1),255)) & " "& LEFT( TRIM(A1),MIN(FIND({" ",","},A1&","))-1))


It gives these results against the above sample :

Artist
#VALUE!
#VALUE!
#VALUE!
H P Zinker
#VALUE!
#VALUE!
#VALUE!
#VALUE!
New Riders Purple Sage


It's hard to see where the error is , but hopefully this will help.


(b) I did try the UDF you sent too , and this works like a dream. I'm
using it against artists in my record collection , all of which have
inverted names. Sometimes of course , it inverts where the group name
has two elements in any case.

EG ;

Browne Jackson correctly becomes Jackson Browne , but Led Zeppelin
incorrectly becomes Zeppelin Led.

Is there a way to avoid this , say by allowing a disqualifying marker in
a third column for the code to read? Perhaps the user could place an X
in the third column causing the code to skip the inversion for that row?

So :

A B
C

Browne Jackson Jackson Brown

Led Zeppelin Led Zeppelin X


Thanks again Ron for your time and expertise.


Best Wishes

 

[Ron Rosenfeld]

Hi Ron

(a) OK I've pasted below sample cell entries that give VALUE errors :

Artist
Browne Jackson
Ellington Duke
Fields Gracie
H P Zinker
Oconnor Hazel
Ifield Frank
Memphis Slim
Miller Glenn
New Riders Purple Sage

This is the formula from B1 :

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))<>1,
A1,TRIM(MID(TRIM(A1),FIND("
",A1),255)) & " "& LEFT( TRIM(A1),MIN(FIND({" ",","},A1&","))-1))


It gives these results against the above sample :

Artist
#VALUE!
#VALUE!
#VALUE!
H P Zinker
#VALUE!
#VALUE!
#VALUE!
#VALUE!
New Riders Purple Sage


It's hard to see where the error is , but hopefully this will help.

The error is that some of the " " got changed to "".

Here is the formula with line feeds introduced into different places
so, hopefully, it will not lose the <space>'s.

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(
TRIM(A1)," ",""))<>1,A1,TRIM(
MID(TRIM(A1),FIND(" ",A1),255))
& " "& LEFT( TRIM(A1),MIN(
FIND({" ",","},A1&","))-1))

(b) I did try the UDF you sent too , and this works like a dream. I'm
using it against artists in my record collection , all of which have
inverted names. Sometimes of course , it inverts where the group name
has two elements in any case.

EG ;

Browne Jackson correctly becomes Jackson Browne , but Led Zeppelin
incorrectly becomes Zeppelin Led.

Is there a way to avoid this , say by allowing a disqualifying marker in
a third column for the code to read? Perhaps the user could place an X
in the third column causing the code to skip the inversion for that row?

==============================
Option Explicit
Function RevLastFirst(s As String, Skip) As String
Dim re As Object, mc As Object
Const sPat As String = "^(\b\S+\b)[^-\w]+(\b\S+\b)$"
Dim sTrimmed As String
sTrimmed = Trim(s)
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
If Len(Skip) > 0 Then re.Pattern = "^$"
re.Global = True
RevLastFirst = re.Replace(sTrimmed, "$2 $1")
End Function
=============================

So you could use
=RevLastFirst(A1,C1)

If you put anything (other than a null string) into C1, it will "Skip"
the conversion.

 

[Colin Hayes]

The error is that some of the " " got changed to "".

Here is the formula with line feeds introduced into different places
so, hopefully, it will not lose the <space>'s.

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(
TRIM(A1)," ",""))<>1,A1,TRIM(
MID(TRIM(A1),FIND(" ",A1),255))
& " "& LEFT( TRIM(A1),MIN(
FIND({" ",","},A1&","))-1))

(b) I did try the UDF you sent too , and this works like a dream. I'm
using it against artists in my record collection , all of which have
inverted names. Sometimes of course , it inverts where the group name
has two elements in any case.

EG ;

Browne Jackson correctly becomes Jackson Browne , but Led Zeppelin
incorrectly becomes Zeppelin Led.

Is there a way to avoid this , say by allowing a disqualifying marker in
a third column for the code to read? Perhaps the user could place an X
in the third column causing the code to skip the inversion for that row?

==============================
Option Explicit
Function RevLastFirst(s As String, Skip) As String
Dim re As Object, mc As Object
Const sPat As String = "^(\b\S+\b)[^-\w]+(\b\S+\b)$"
Dim sTrimmed As String
sTrimmed = Trim(s)
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
If Len(Skip) > 0 Then re.Pattern = "^$"
re.Global = True
RevLastFirst = re.Replace(sTrimmed, "$2 $1")
End Function
=============================

So you could use
=RevLastFirst(A1,C1)

If you put anything (other than a null string) into C1, it will "Skip"
the conversion.

Hi Ron

OK thanks again.

The formula is working perfectly now , with no error from Excel. As you
say , the email transfer clearly did cause character changes which
undermined the code. All is well now , however.

The amendment to the UDF to allow a disqualifying entry solves the issue
of unnecessary inversion very neatly. Again , perfect first time.

You clearly have impressive and considerable expertise , and I'm
grateful to you for your assistance in the puzzles I had.


Best wishes

 

[Ron Rosenfeld]

Hi Ron

OK thanks again.

The formula is working perfectly now , with no error from Excel. As you
say , the email transfer clearly did cause character changes which
undermined the code. All is well now , however.

The amendment to the UDF to allow a disqualifying entry solves the issue
of unnecessary inversion very neatly. Again , perfect first time.

You clearly have impressive and considerable expertise , and I'm
grateful to you for your assistance in the puzzles I had.

You're more than welcome. Glad to help. Thanks for the feedback.

By the way, the formula could be modified to function similarly to the
UDF:

=IF(OR(LEN(C1)>0,
LEN(TRIM(A1))-LEN(SUBSTITUTE(
TRIM(A1)," ",""))<>1),A1,TRIM(
MID(TRIM(A1),FIND(" ",A1),255))
& " "& LEFT( TRIM(A1),MIN(
FIND({" ",","},A1&","))-1))

But I think the UDF is easier to understand, modify and debug.

 

[Colin Hayes]

You're more than welcome. Glad to help. Thanks for the feedback.

By the way, the formula could be modified to function similarly to the
UDF:

=IF(OR(LEN(C1)>0,
LEN(TRIM(A1))-LEN(SUBSTITUTE(
TRIM(A1)," ",""))<>1),A1,TRIM(
MID(TRIM(A1),FIND(" ",A1),255))
& " "& LEFT( TRIM(A1),MIN(
FIND({" ",","},A1&","))-1))

But I think the UDF is easier to understand, modify and debug.

Hi Ron

Yes , that does the job very well also. I think the modification is a
very useful extension. I agree that the UDF is a little more
bullet-proof though , even given the mysteries of Regexp.

Thanks again.

Best wishes

 

[Ron Rosenfeld]

Hi Ron

Yes , that does the job very well also. I think the modification is a
very useful extension. I agree that the UDF is a little more
bullet-proof though , even given the mysteries of Regexp.

Thanks again.

Best wishes

The UDF was written when there were lots of possibilities for how the
words were being separated.

If, as in your last example, the words are separated only by
<space>'s, then this non-Regexp UDF will work just as well:

============================
Option Explicit
Function RevLastFirst(s As String, Skip) As String
Dim sTemp As Variant
sTemp = Split(WorksheetFunction.Trim(s), " ")
If UBound(sTemp) <> 1 Or _
Len(Skip) > 0 Then
RevLastFirst = s
Exit Function
Else
RevLastFirst = sTemp(1) & " " & sTemp(0)
End If
End Function
======================

or, the Regex pattern in the Regex version can be simplified:

==============================
Option Explicit
Function RevLastFirst(s As String, Skip) As String
Dim re As Object, mc As Object
Const sPat As String = "^(\w+)\s+(\w+)$"
Dim sTrimmed As String
sTrimmed = Trim(s)
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
If Len(Skip) > 0 Then re.Pattern = "^$"
re.Global = True
RevLastFirst = re.Replace(sTrimmed, "$2 $1")
End Function
==========================

 

[Colin Hayes]

The UDF was written when there were lots of possibilities for how the words were
being separated.

Hi Ron

Interesting variations on the theme. Presently , I'm placing an 'x' in
column C against names I don't need to be reversed , and both solutions
undo the inversion in column B.

It occurs to me that a further *very* useful extension would be to have
the UDF or formula replicate the disqualifying character in C to match
each time the identical content of cell B occurs.

For example :

In column A I have

Led Zeppelin

In column B , it is inverted to

Zeppelin Led

I place 'x' in column C to undo the inversion.

The program then places 'x' in column C against all further occurrences
it finds of Zeppelin Led in B.

In this way , I would only need to put the one disqualifying mark
against a given string , and the program would echo by placing the
disqualifying character in C for each time the string appears in B.

Is this possible , do you think? Or some variation which would achieve
the same goal?



Best Wishes



BTW

This is the UDF I'm using per your suggestion :

Option Explicit
Function RevLastFirst(s As String, Skip) As String
Dim re As Object, mc As Object
Const sPat As String = "^(\b\S+\b)[^-\w]+(\b\S+\b)$"
Dim sTrimmed As String
sTrimmed = Trim(s)
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
If Len(Skip) > 0 Then re.Pattern = "^$"
re.Global = True
RevLastFirst = re.Replace(sTrimmed, "$2 $1")
End Function

with the formula =RevLastFirst(A1,C2)