Change around surname and name
 

Hi

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

--
Hth

Kassie Kasselman
Change xxx to hotmail

Change around surname and name
 

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''s Student - gsnu200728


"kassie" wrote:

Hi

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

--
Hth

Kassie Kasselman
Change xxx to hotmail

Change around surname and name
 

Thanks Gary!!!!!!!!
--
Hth

Kassie Kasselman
Change xxx to hotmail


"Gary''s Student" wrote:

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''s Student - gsnu200728


"kassie" wrote:

Hi

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

--
Hth

Kassie Kasselman
Change xxx to hotmail

Change around surname and name
 

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
--
Hth

Kassie Kasselman
Change xxx to hotmail


"Gary''s Student" wrote:

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''s Student - gsnu200728


"kassie" wrote:

Hi

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

--
Hth

Kassie Kasselman
Change xxx to hotmail

Change around surname and name
 

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??
--
Gary''s Student - gsnu200728


"kassie" wrote:

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
--
Hth

Kassie Kasselman
Change xxx to hotmail


"Gary''s Student" wrote:

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''s Student - gsnu200728


"kassie" wrote:

Hi

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

--
Hth

Kassie Kasselman
Change xxx to hotmail

Change around surname and name
 

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


--
Gary''s Student - gsnu200728


"kassie" wrote:

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
--
Hth

Kassie Kasselman
Change xxx to hotmail


"Gary''s Student" wrote:

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''s Student - gsnu200728


"kassie" wrote:

Hi

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

--
Hth

Kassie Kasselman
Change xxx to hotmail

Change around surname and name
 

On Sat, 9 Jun 2007 02:22:00 -0700, kassie wrote:

Hi

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

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

Change around surname and name
 

On Sat, 09 Jun 2007 13:00:58 -0400, Ron Rosenfeld
wrote:

On Sat, 9 Jun 2007 02:22:00 -0700, kassie wrote:

Hi

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

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

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

Change around surname and name
 

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" wrote:

On Sat, 09 Jun 2007 13:00:58 -0400, Ron Rosenfeld
wrote:

On Sat, 9 Jun 2007 02:22:00 -0700, kassie wrote:

Hi

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

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

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