Change around surname and name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

In several lists, data is reflected as Initials Surname. How can I change
it to Surname Initials, by using a macro?
 
You can do this with just a formula, but the folowing macro:

Sub kas()
For Each r In Selection
s = Split(r.Value, " ")
u = UBound(s)
r.Value = s(u)
For j = u - 1 To 0 Step -1
r.Value = r.Value & " " & s(j)
Next
Next
End Sub

will handle both multiple and single initials before the lastname.
 
Gary, something's wrong here. When I run this macro, it repeats the surname
twice for eg JJ Smith, and 3 times for eg JJ da Silva. It throws away the
initials though
 
Hi Kassie:

If I enter:
JJ da Silva
in a cell, select the cell and run the macro, I get:

Silva da JJ



Is this what you are seeing??
 
I found the initials problem. Here is a better verson:

Sub kas()
For Each r In Selection
s = Split(r.Value, " ")
u = UBound(s)
r.Value = s(u)
If u > 0 Then
For j = 0 To u - 1
r.Value = r.Value & " " & s(j)
Next
End If
Next
End Sub


As a test:

Powers
J Powers
J D Powers
Cher
Sonny & Cher
1 2 3 4 9999


changed into:

Powers
Powers J
Powers J D
Cher
Cher Sonny &
9999 1 2 3 4
 
Hi

In several lists, data is reflected as Initials Surname. How can I change
it to Surname Initials, by using a macro?
Click to expand...

In this UDF, "Initials" is defined as on or more sequences of one or two
capital letters (followed by a <space>). The Initials are placed at the end of
the string.

The following is the result:

Powers Powers
J Powers Powers J
J D Powers Powers J D
Cher Cher
Sonny & Cher Sonny & Cher
1 2 3 4 9999 1 2 3 4 9999
De La Smith De La Smith
JJ De La Smith De La Smith JJ

====================================
Function SurnameFirst(str As String) As String
Dim oRegex As Object
Const sPattern As String = "(([A-Z]{1,2}\s)*)(.*)"

Set oRegex = CreateObject("VBScript.RegExp")

With oRegex
.Global = True
.IgnoreCase = False
.Pattern = sPattern
End With
SurnameFirst = oRegex.Replace(str, "$3 $1")
End Function
===========================


--ron
 
Hi

In several lists, data is reflected as Initials Surname. How can I change
it to Surname Initials, by using a macro?
Click to expand...

In this UDF, "Initials" is defined as on or more sequences of one or two
capital letters (followed by a <space>). The Initials are placed at the end of
the string.

The following is the result:

Powers Powers
J Powers Powers J
J D Powers Powers J D
Cher Cher
Sonny & Cher Sonny & Cher
1 2 3 4 9999 1 2 3 4 9999
De La Smith De La Smith
JJ De La Smith De La Smith JJ

====================================
Function SurnameFirst(str As String) As String
Dim oRegex As Object
Const sPattern As String = "(([A-Z]{1,2}\s)*)(.*)"

Set oRegex = CreateObject("VBScript.RegExp")

With oRegex
.Global = True
.IgnoreCase = False
.Pattern = sPattern
End With
SurnameFirst = oRegex.Replace(str, "$3 $1")
End Function
===========================


--ron
Click to expand...

If, instead of a function, you wish to have a Sub do the same thing, you can
use this:

=======================================
Sub SurnInit()
Dim c As Range

For Each c In Selection
c.Value = SurnameFirst(c.Text)
Next c

End Sub

Function SurnameFirst(str As String) As String
Dim oRegex As Object
Const sPattern As String = "(([A-Z]{1,2}\s)*)(.*)"

Set oRegex = CreateObject("VBScript.RegExp")

With oRegex
.Global = True
.IgnoreCase = False
.Pattern = sPattern
End With
SurnameFirst = oRegex.Replace(str, "$3 $1")
End Function
=================================================
--ron
 
Hi Gary and Ron,

If you're good, you're good, that's very true! If you're old, you're
sometimes stupid, that's also true.

Gary's first reply works like a bomb, provided that you use the "j" he used,
instead of the "u" I went and typed in!

I also found that, if you want to keep something like JJ da Silva as da
Silva JJ, instead of Silva JJ da, all you have to do, is add a comma and a 2
to the argument s = Split(r.Value, " ") so as to read s = Split(r.Value,"
",2)

Thanks for your responses guys, you are great!
--


Kassie Kasselman
Change xxx to hotmail


Ron Rosenfeld said:
Hi

In several lists, data is reflected as Initials Surname. How can I change
it to Surname Initials, by using a macro?
Click to expand...

In this UDF, "Initials" is defined as on or more sequences of one or two
capital letters (followed by a <space>). The Initials are placed at the end of
the string.

The following is the result:

Powers Powers
J Powers Powers J
J D Powers Powers J D
Cher Cher
Sonny & Cher Sonny & Cher
1 2 3 4 9999 1 2 3 4 9999
De La Smith De La Smith
JJ De La Smith De La Smith JJ

====================================
Function SurnameFirst(str As String) As String
Dim oRegex As Object
Const sPattern As String = "(([A-Z]{1,2}\s)*)(.*)"

Set oRegex = CreateObject("VBScript.RegExp")

With oRegex
.Global = True
.IgnoreCase = False
.Pattern = sPattern
End With
SurnameFirst = oRegex.Replace(str, "$3 $1")
End Function
===========================


--ron
Click to expand...

If, instead of a function, you wish to have a Sub do the same thing, you can
use this:

=======================================
Sub SurnInit()
Dim c As Range

For Each c In Selection
c.Value = SurnameFirst(c.Text)
Next c

End Sub

Function SurnameFirst(str As String) As String
Dim oRegex As Object
Const sPattern As String = "(([A-Z]{1,2}\s)*)(.*)"

Set oRegex = CreateObject("VBScript.RegExp")

With oRegex
.Global = True
.IgnoreCase = False
.Pattern = sPattern
End With
SurnameFirst = oRegex.Replace(str, "$3 $1")
End Function
=================================================
--ron
Click to expand...
 
Back
Top