| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rearranging_names
hi all!
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
You can use this formula to reverse the initials and surnames...
=MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)))+1,LEN(A1)) -- Rick (MVP - Excel) "via135" wrote in message ... hi all! 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
Try this:
=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))&"."&A1,LEN(A1)+(COUNTIF(A1,"*.*") 0)) "via135" wrote: hi all! 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 . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
On Apr 3, 11:21*pm, Teethless mama
wrote: Try this: =LEFT(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))&"."&A1,LEN(A1)+(COUNTIF(A1,"*.*") 0)) "via135" wrote: hi all! 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 . hello Teethless mama..! i am getting the result like this... M C R M L S |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
On Apr 3, 10:55*pm, "Rick Rothstein"
wrote: You can use this formula to reverse the initials and surnames... =MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)))+1,LEN(A1)) -- Rick (MVP - Excel) "via135" wrote in message ... hi all! 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 hi Rothstein.! i am getting the results like.... ..GUPTA. ..S.BANERJEE.C. RAMAN K.S.MOHAN.M.M T.PRABA.L ..LAL. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
I cannot consistently get the output you are showing. If I remove the "+1",
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" wrote in message ... On Apr 3, 10:55 pm, "Rick Rothstein" wrote: You can use this formula to reverse the initials and surnames... =MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)))+1,LEN(A1)) -- Rick (MVP - Excel) "via135" wrote in message ... hi all! 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 hi Rothstein.! i am getting the results like.... .GUPTA. .S.BANERJEE.C. RAMAN K.S.MOHAN.M.M T.PRABA.L .LAL. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
Your formula is missing a first letter of the surnames
"Rick Rothstein" wrote: You can use this formula to reverse the initials and surnames... =MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)))+1,LEN(A1)) -- Rick (MVP - Excel) "via135" wrote in message ... hi all! 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 . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
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) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
I do note, however, that with properly entered data, the trailing "dot" is
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" wrote in message ... I cannot consistently get the output you are showing. If I remove the "+1", 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" wrote in message ... On Apr 3, 10:55 pm, "Rick Rothstein" wrote: You can use this formula to reverse the initials and surnames... =MID(A1&"."&A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)))+1,LEN(A1)) -- Rick (MVP - Excel) "via135" wrote in message ... hi all! 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 hi Rothstein.! i am getting the results like.... .GUPTA. .S.BANERJEE.C. RAMAN K.S.MOHAN.M.M T.PRABA.L .LAL. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
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) "Rick Rothstein" wrote in message ... 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) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
rearranging_names
On Apr 4, 12:32*am, "Rick Rothstein"
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) "Rick Rothstein" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| rearranging_names | Excel Worksheet Functions | |||
Linear Mode
