rearranging_names

  • Thread starter Thread starter via135
  • Start date Start date
V

via135

hi all!

i am having names in COL -A
all begining with intials followed by the name
and some names without initials as under:

M.GUPTA
C.K.S.BANERJEE
RAMAN
M.M.K.S.MOHAN
L.T.PRABA
S.LAL

and so on..!

what i want is to rearrange
the names to begin with the name
followed by initials..like

GUPTA.M.
BANERJEE.C.K.S.
RAMAN
MOHAN.M.M.K.S.
PRABA.L.T.
LAL.S.

so that it would be easier for
sorting the names..!
any help please?

-via135
 
You can use this formula to reverse the initials and surnames...

=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT("
",99)),99)))+1,LEN(A1))
 
Try this:

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT("
",99)),99))&"."&A1,LEN(A1)+(COUNTIF(A1,"*.*")>0))
 
Try this:

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT("
",99)),99))&"."&A1,LEN(A1)+(COUNTIF(A1,"*.*")>0))
Click to expand...

hello Teethless mama..!
i am getting the result like this...

M
C
R
M
L
S
 
You can use this formula to reverse the initials and surnames...

=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT("
",99)),99)))+1,LEN(A1))
Click to expand...

hi Rothstein.!

i am getting the results like....

..GUPTA.
..S.BANERJEE.C.
RAMAN
K.S.MOHAN.M.M
T.PRABA.L
..LAL.
 
I cannot consistently get the output you are showing. If I remove the "+1",
I can get some of them, if I add spaces to your entries, I can get other,
but I can't seem to duplicate what you show. My suggestion is to first make
sure your entries don't have leading or trailing "invisible" characters
(such as the space character or the ASCII 160 non-breaking space character).
Next, make sure you are using the *exact* formula I posted (copy/paste it,
don't re-type it). Then let us know whether it is working for you or not.
Note that I tested the formula on the sample data you posted and it does
work.
 
Your formula is missing a first letter of the surnames

????

That is not the case on any of the tests I've performed here. I do note that
the trailing "dot" is missing though. Try this formula and make sure you
don't have a trailing blank space after the name...

=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT("
",99)),99)))+1,LEN(A1)+1)
 
I do note, however, that with properly entered data, the trailing "dot" is
missing from those names with initials. So you should use this formula
instead of the one I originally posted...

=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",
REPT(" ",99)),99)))+1,LEN(A1)+(NOT(ISERR(FIND(".",A1)))))
 
Uh, this formula is the correct one to use...

=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",
REPT(" ",99)),99)))+1,LEN(A1)+(NOT(ISERR(FIND(".",A1)))))
 
Uh, this formula is the correct one to use...

=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",
REPT(" ",99)),99)))+1,LEN(A1)+(NOT(ISERR(FIND(".",A1)))))
Click to expand...

yes..
=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",
REPT(" ",99)),99)))+1,LEN(A1)+(NOT(ISERR(FIND(".",A1)))))
this one works fine..!

thks Rothstein for fixing up the problem..! thks again

-via135
 
Another option, using a macro, is:

Code:
--------------------

Sub organize()
  Dim i As Long, j As Long
  For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
  j = InStrRev(Cells(i, 1).Value, ".")
  If j > 0 Then
  Cells(i, 1).Value = Mid(Cells(i, 1).Value, j + 1, 255) & _
  "." & Left(Cells(i, 1).Value, j)
  End If
  Next i
  End Sub
--------------------

Assumes values are in A1:Axx.  Paste this code into the worksheet
module with your data (ALT+F11 to open VB Editor).

--
Paul

- Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=192696

http://www.thecodecage.com/forumz
Click to expand...

thks Paul..!

the macro also works like a charm..!

-via135
 
Back
Top