rearranging_names
via135
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
Paul
Assuming your names are in A1:Axx, then in B1:Bxx you could use:
=IF(ISNUMBER(FIND(".",A1)),SUBSTITUTE(A1,".","^",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))),A1)
and then in C1:Cxx use:
=IF(ISNUMBER(FIND("^",B1)),MID(B1,FIND("^",B1)+1,255)&"."&SUBSTITUTE(LEFT(B1,FIND("^",B1)),"^","."),B1)
You could then copy the final values in column C and use PasteSpecial
over the values in column A. Then delete columns B & C.
You could also use VBA, and without having coded it the InStrRev method
might work quite well.
--
Paul
- Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192696
Rick Rothstein
=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT("
",99)),99)))+1,LEN(A1))
--
Rick (MVP - Excel)
"via135" <via...@gmail.com> wrote in message
news:42995b4a-43db-4bca...@k13g2000yqe.googlegroups.com...
Teethless mama
=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT("
",99)),99))&"."&A1,LEN(A1)+(COUNTIF(A1,"*.*")>0))
"via135" wrote:
> .
>
via135
hello Teethless mama..!
i am getting the result like this...
M
C
R
M
L
S
via135
hi Rothstein.!
i am getting the results like....
.GUPTA.
.S.BANERJEE.C.
RAMAN
K.S.MOHAN.M.M
T.PRABA.L
.LAL.
Rick Rothstein
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.
--
Rick (MVP - Excel)
"via135" <via...@gmail.com> wrote in message
news:701ce179-0acb-4324...@i25g2000yqm.googlegroups.com...
Teethless mama
"Rick Rothstein" wrote:
> .
>
Rick Rothstein
????
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)
Rick Rothstein
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)))))
--
Rick (MVP - Excel)
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:OKwHl#10KHA...@TK2MSFTNGP05.phx.gbl...
Rick Rothstein
=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",
REPT(" ",99)),99)))+1,LEN(A1)+(NOT(ISERR(FIND(".",A1)))))
--
Rick (MVP - Excel)
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:ug70bO2...@TK2MSFTNGP04.phx.gbl...
Paul
Both Teethless and Rick's functions seem to work fine (same results as
mine except that Rick's leaves off the final ".").
This is from copying the "original" data from the webpage and pasting
it into Excel (no hidden spacing exists, etc). If you're not getting
the same results, your data has to be different in some way than what
you're providing in your original post. (hidden characters, carriage
returns, etc.)
Rick Rothstein
--
Rick (MVP - Excel)
"Paul" <Paul....@thecodecage.com> wrote in message
news:Paul....@thecodecage.com...
Paul
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).
via135
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> 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)))))
>
> --
> Rick (MVP - Excel)
>
>
> news:ug70bO2...@TK2MSFTNGP04.phx.gbl...
>
>
>
> >> 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)
>
> > --
> > Rick (MVP - Excel)
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
via135
thks Paul..!
the macro also works like a charm..!
-via135