| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
