DRon
Yessssssss. It works perfect and thanks for ur guidance. I hv replied to
the original post answering if the cell has two names. Further I hv also
sent one email also requesting for little bit of explanations. Subsequently
I managed to find on google about regexp and figured out a bit.

This is will be complete if you could pls clarify how u arrived "$3 $1" in
the code
"""oRegex.Replace(c.Text, "$3 $1")""""

wrgds/captgnvr

"Ron Rosenfeld" wrote:

On Sat, 21 Jul 2007 23:42:00 -0700, CAPTGNVR
wrote:

DEAR ALL,

I copied about 10 names from the thousands i have and ran the below code
(not my brains but got assistance thanks to Mr. Ron Rosenfield, Gary
student, Jim Thomlinson, Kevin Jones, Mike and Peter Richardson. Pls forgive
me for not following up all your help and assitence on account of my
ignorance of the delay in uploading by google.

By the time I was intimated, the thread has gone cold. Consequently I read
up lot on regexes and sort of got a hang of what the code is about.

Some sugested with inStrRev and split for which I get compile error as my
company provided only excel97.


I AM USING EXCEL97.

FIRST CODE:

This below code did shift the initials to the end of the name but ran once
and does not work again.

Public Sub ConvertNames()
'THIS WORKED FOR ONCE AND SECOND TIME NOT WORKING WHAT COULD BE REASON
Dim RegEx As Object
Dim Cell As Range
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.MultiLine = True
RegEx.IgnoreCase = True
RegEx.Pattern = "^([a-z..]*\.)([a-z]+)$"

For Each Cell In Selection
Cell = RegEx.Replace(Cell, "$2 $1")
Next Cell

End Sub


SECOND CODE:

This below code works fine but the initials goes in the middle if I have two
names.

Option Explicit
Sub MoveInit()

'given by ron rosenfield
Dim c As Range
Dim oRegex As Object
Dim oMatchCollection As Object
Dim i As Long
Const sPattern As String = "(([A-Z]\.\s?)*)(\w+)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = True
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).Value = oRegex.Replace(c.Text, "$3 $1")
Next c
End Sub

So pls help me in
A) What must have gone wrong in First code.
B) What must be the code to put the initials at the end if the cell has two
names like
A.S.D.PETER GREG.

Needless to say that I will be watching for all the post continuously all
day long unlike my earlier posts on this same subject.

brgds/captgnvr


Had you read my first response completely, you would have seen that I asked you
that very question of what you wanted for output if there were multiple names.


Try this for your multiple names issue:

========================================
Option Explicit
Sub MoveInit()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "(([A-Z]\.\s?)*)(.*)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = False
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).Value = oRegex.Replace(c.Text, "$3 $1")
Next c
End Sub
============================================
--ron