rearranging_names
in [Functions]
|
From: via135 via OfficeKB.com on 1 Apr 2010 12:12 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1
From: Ms-Exl-Learner on 1 Apr 2010 12:37 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. -------------------- (Ms-Exl-Learner) -------------------- "via135 via OfficeKB.com" <u23552(a)uwe> wrote in message news:a5e30483f8766(a)uwe... > 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 > > -- > Message posted via OfficeKB.com > http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1 >
From: Rick Rothstein on 1 Apr 2010 13:53 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) "Ms-Exl-Learner" <msxl(a)live.com> wrote in message news:uvDummb0KHA.3676(a)TK2MSFTNGP05.phx.gbl... > 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. > > -------------------- > (Ms-Exl-Learner) > -------------------- > > > "via135 via OfficeKB.com" <u23552(a)uwe> wrote in message > news:a5e30483f8766(a)uwe... >> 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 >> >> -- >> Message posted via OfficeKB.com >> http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1 >> > >
From: Rick Rothstein on 1 Apr 2010 13:58 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) "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:#FjrNRc0KHA.6108(a)TK2MSFTNGP06.phx.gbl... > 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) > > > > "Ms-Exl-Learner" <msxl(a)live.com> wrote in message > news:uvDummb0KHA.3676(a)TK2MSFTNGP05.phx.gbl... >> 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. >> >> -------------------- >> (Ms-Exl-Learner) >> -------------------- >> >> >> "via135 via OfficeKB.com" <u23552(a)uwe> wrote in message >> news:a5e30483f8766(a)uwe... >>> 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 >>> >>> -- >>> Message posted via OfficeKB.com >>> http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1 >>> >> >>
From: Teethless mama on 1 Apr 2010 14:39 Try this: =LEFT(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))&"."&A1,LEN(A1)) "via135 via OfficeKB.com" wrote: > 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 > > -- > Message posted via OfficeKB.com > http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1 > > . >
|
Next
|
Last
Pages: 1 2 Prev: how do I sort by picked-up date in Excel Next: How do I make first row sort in table |