rearranging_names

[via135 via OfficeKB.com]

hi all!

i am having thousands of names in col A
the problem is the initials are before
the names..!

for example as

A.K.PRAVEEN
C.GUPTA
G.K.S.RICHARD
M.PRAVEEN
L.M.DAS

and so on..!

how can i convert the data
with initials after the name
in each cell like...

PRAVEEN.A.K.
GUPTA.C.
RICHARD.G.K.S.
PRAVEEN.M.
DAS.L.M.

so that it would be easier for
sorting the records..!

any hlp..?


-via135

 

[Ms-Exl-Learner]

Assuem that you are having the below data in A Column like the below:-

A Column
Row1 A.K.PRAVEEN
Row2 C.GUPTA
Row3 G.K.S.RICHARD
Row4 M.PRAVEEN
Row5 L.M.DAS


Paste this formula in B1 cell
=MID(A1,FIND("^^",SUBSTITUTE(A1,".","^^",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+1,255)&"."&LEFT(A1,FIND("^^",SUBSTITUTE(A1,".","^^",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))

Copy the B1 cell and paste it for the remaining cell of B Column depends
upon the A column data. Change the Cell refence A1 to your desired cell, if
required.

 

[Rick Rothstein]

Here is a little bit shorter formula that can used...

=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))&"."&SUBSTITUTE(A1,
TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)),"")

--
Rick (MVP - Excel)

Assuem that you are having the below data in A Column like the below:-

A Column
Row1 A.K.PRAVEEN
Row2 C.GUPTA
Row3 G.K.S.RICHARD
Row4 M.PRAVEEN
Row5 L.M.DAS


Paste this formula in B1 cell
=MID(A1,FIND("^^",SUBSTITUTE(A1,".","^^",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+1,255)&"."&LEFT(A1,FIND("^^",SUBSTITUTE(A1,".","^^",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))

Copy the B1 cell and paste it for the remaining cell of B Column depends
upon the A column data. Change the Cell refence A1 to your desired cell,
if required.

 

[Rick Rothstein]

Here is an even shorter formula that can be used...

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

--
Rick (MVP - Excel)

Here is a little bit shorter formula that can used...

=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))&"."&SUBSTITUTE(A1,
TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)),"")

 

[Teethless mama]

Try this:

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))&"."&A1,LEN(A1))

 

[Rick Rothstein]

Your formula leaves off the trailing "dot". Here is the modification to it
in order to retain that trailing "dot"...

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))&"."&A1,LEN(A1)+1)

 

[Ron Rosenfeld]

hi all!

i am having thousands of names in col A
the problem is the initials are before
the names..!

for example as

A.K.PRAVEEN
C.GUPTA
G.K.S.RICHARD
M.PRAVEEN
L.M.DAS

and so on..!

how can i convert the data
with initials after the name
in each cell like...

PRAVEEN.A.K.
GUPTA.C.
RICHARD.G.K.S.
PRAVEEN.M.
DAS.L.M.

so that it would be easier for
sorting the records..!

any hlp..?


-via135

Here is a User Defined Function that might do what you want.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula:

B1: =RevInits(A1)

and fill down as far as necessary


============================
Option Explicit
Function RevInits(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\."
If re.test(s) = True Then
re.Pattern = "^((.\.)*)(.+)$"
RevInits = re.Replace(s, "$3.$1")
Else
RevInits = s
End If
End Function
===================================
--ron

 

[Paul]

In the future, please refrain from cross-posting your question a
multiple newsgroups/forums. The thread you started her
(http://tinyurl.com/ycr5grf) has multiple responses. Cross-postin
wastes the time of volunteers who try to help, only to find out you'v
received an answer elsewhere.

hi all!

i am having thousands of names in col A
the problem is the initials are before
the names..!

for example as

A.K.PRAVEEN
C.GUPTA
G.K.S.RICHARD
M.PRAVEEN
L.M.DAS

and so on..!

how can i convert the data
with initials after the name
in each cell like...

PRAVEEN.A.K.
GUPTA.C.
RICHARD.G.K.S.
PRAVEEN.M.
DAS.L.M.

so that it would be easier for
sorting the records..!

any hlp..?


-via135

(http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1)

 

[Rick Rothstein]

The thread at the link you posted appears to simply be a copy of this thread
here and not a new or different cross posted thread.

 

[via135 via OfficeKB.com]

In the future, please refrain from cross-posting your question a
multiple newsgroups/forums. The thread you started her
(http://tinyurl.com/ycr5grf) has multiple responses. Cross-postin
wastes the time of volunteers who try to help, only to find out you'v
received an answer elsewhere.

via135 via OfficeKB.com;687896 Wrote:

hi all!

i am having thousands of names in col A
the problem is the initials are before

[quoted text clipped - 30 lines]

Message posted via OfficeKB.com
'Excel - using Excel worksheet functions

(http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1)

first i posted this thread here on 01/04/2010 since then i couldn't get
this site for 2 days ie) 02/04/2010 & 03/04/2010.

then only i preferred to post it here
http://groups.google.com/group/micr...898d7e1455c/3efe0dd71ce9d04d#3efe0dd71ce9d04d


and got the solution from You & Tothstein..!

Hence pl don't treat it as a purported cross posting..!

thks for the help..!

-via135