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

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

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

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

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

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

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

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

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