D/Don
Ur advice reg caps noted. It is bcos of my eyesight and the fonts in these
groups are so small, some important ones i use caps.
I was just waiting for some guidance and as u sugested I tried with the
function. But the whole name also gets reversed. For example A.Z.RED becomes
DER.Z.A.
As I mentioned the second works fine but puts the initials in the middle.
so pls sugest steps to change the name A.S.D.PETER GREG to PETER GREG A.S.D.
For you information some names have space after the initials and in some no
space.
Looking forward to knowing ur advice n thnks for the first step.
"Don Guillett" wrote:
> Please don't type in all CAPS (including subject line) as it is considered
> shouting and rude.
> Put this in a REGULAR module.
> Public Function StrReverse(reverseString As String) As String
> Dim i As Long
> For i = Len(reverseString) To 1 Step -1
> StrReverse = StrReverse & Mid(reverseString, i, 1)
> Next i
> End Function
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "CAPTGNVR" <(E-Mail Removed)> wrote in message
> news:FD95BD93-1CBC-4F20-855B-(E-Mail Removed)...
> > 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
>
>
Similar Threads
