|
rearranging_names
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.a...tions/201004/1 |
rearranging_names
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,FI ND("^^",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@uwe wrote in message news:a5e30483f8766@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.a...tions/201004/1 |
rearranging_names
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" wrote in message ... 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,FI ND("^^",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@uwe wrote in message news:a5e30483f8766@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.a...tions/201004/1 |
rearranging_names
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" wrote in message ... 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" wrote in message ... 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,FI ND("^^",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@uwe wrote in message news:a5e30483f8766@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.a...tions/201004/1 |
rearranging_names
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.a...tions/201004/1 . |
rearranging_names
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) -- Rick (MVP - Excel) "Teethless mama" wrote in message ... 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.a...tions/201004/1 . |
rearranging_names
Paul wrote:
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.a...tions/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/micro...fe0dd71ce9d04d and got the solution from You & Tothstein..! Hence pl don't treat it as a purported cross posting..! thks for the help..! -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201004/1 |
| All times are GMT +1. The time now is 03:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com