Reverse name in excel with upper case

[shrinivasmj]

hi,

i need to reverse name excel data in reverse format with upper case as shown below.

INPUT
[TABLE="class: cms_table, width: 280"]
<tbody>[TR]
[TD]anil kapoor singh[/TD]
[/TR]
[TR]
[TD]kiran kumar sen gee[/TD]
[/TR]
[TR]
[TD]super kumar sing meer natak t.
[/TD]
[/TR]
</tbody>[/TABLE]


OUT PUT
[TABLE="class: cms_table, width: 245"]
<tbody>[TR]
[TD]SINGH,ANIL KAPOOR[/TD]
[/TR]
[TR]
[TD]SEN GEE,KIRAN KUMAR
[/TD]
[/TR]
[TR]
[TD]MEER NATAK T,SUPER KUMAR SING
[/TD]
[/TR]
</tbody>[/TABLE]


INSTRUCTION TO DO

[TABLE="class: cms_table, width: 287"]
<tbody>[TR]
[TD]up to-3 words last letter with comma[/TD]
[/TR]
[TR]
[TD]4 to 5 words last 2 letters with comma
[/TD]
[/TR]
[TR]
[TD]>6 words last 3 words with comma
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD]regards
srinivasmj[/TD]
[/TR]
</tbody>[/TABLE]

 

[facethegod]

If i take a name as

Kumar anil t. sing
the out put is
SING,KUMAR ANIL T.

IT SHOULD COME AS
T. SING,KUMAR ANIL

try this..

UPPER(MID(TRIM(A1)&","&TRIM(A1),FIND("$",SUBSTITUTE(TRIM(A1)," ","$",LOOKUP(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1,{0,4,6},(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))-{0,1,2})))+1,LEN(TRIM(A1))))

HOPE this helps..

Have you tried the formula i posted above?

 

[shrinivasmj]

thanks its work

need small change

current out put is
[TABLE="width: 287"]
<colgroup><col width="127"><col width="160"></colgroup><tbody>[TR]
[TD="width: 127"]sunil kumar t. k. -
[/TD]
[TD="width: 160"]T. K.,SUNIL KUMAR
[/TD]
[/TR]
</tbody>[/TABLE]

need like this as out put T.K,SUNIL KUMAR no space after . fullstop

 

[shrinivasmj]

HI, can u help in getting the vb done for my excel sheet

*.Need to creat a <acronym title="vBulletin">vb</acronym> or macro in to the excel workbook with short key to run the program for all the pages in excel and each page out put should creat a new note pad and save the format by files name.

Sheet1.

i have a name data in row G3 can go upto 200 name

i need to copy column from H3 to H13 and if data there from H14TO H24 Need to copy the every 11 block column till end if data found , and paste in end of
G25 as seen in sheet1A.

*.need to repeat copy paste I3 column data till end and paste in G, column from G47

again copy J3 column and paste in G91 as shown in sheet1A.

need to copy and paste all G3 row data to column wise in G. shown in sheet1A.

*.Need to remove all text once from start and ends with / in column G3.there are code to reffer the name and address.

eg. a.b/ b.f/ a.b.c.d/ can see from column C4. removed data shown in sheet1B in G3 column.

trim G, column to remove unwanted space .

Then need to delete comma ( , ) end of every line in G, column data. as seen in Sheet1B.

Used formula to remove comma =LEFT(G3,LEN(G3)-(RIGHT(G3)=","))

need to Assign code for E1 column to Till find next data in column G3 on finded data in G24 ,then need to chang to till
find next data in G3 on again find in G47 need to change E column to till find net data in G3 column from G47.
need to do till the end of G3 Blank to if find all 11 block blank and delete the bottom row from Blank .

Code changed as shown in column E ,

* Sheet1C need to sort D,E,G and Delet blank data from G, column with D,E column data.

Result shown in Sheet1C.

* Sheet1D need to change all Name to upper case in reverse format with condaction eg. shown below

anil kapoor singh SINGH,ANIL KAPOOR
kiran kumar gee tie GEE TIE,KIRAN KUMAR
super kumar meer timmer natak t TIMMER NATAK T,SUPER KUMAR MEER

using this excel formula- =UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),(1+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>2)+(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>4))*99))&","&A1,LEN(A1)))

and take F1 and F2 to G1 and G2. then delete column F ,
need to merge E, column and F,column showm below
=CONCATENATE(E3,F3) as in sheet1D.

*Sheet1E.
FINAL STAGE

Creat a Note Pad by File Name shown in F1 , copy F, column and paste in note pad and save all the excel sheet data one by one new notepad in folder name by excel sheet,

please help in doing <acronym title="vBulletin">vb</acronym> ,macro or .net program were i can run in single command.

file link to download excel file .

( http://www.filesharesite.com/files/2...TO_NOTEPAD.xls)

http://www.filesharesite.com/deletei...9&k=3789848390
Regards

srinivas
9742945611
india

 

[shrinivasmj]

hi need some changes The old formula output is N,J.K,TINYI Want the old formula to add this inputinput is tiny n.j.k.out put should come J.K,TINY N.any single text with full stop need to take as separate textwaiting for replay

 

[shrinivasmj]

hithanks the formula is working need small update need to break the name whit full stop in name as input is Plonas t.m.j.The out put should be M.J,PLONAS T.

 

[shrinivasmj]

hi

=UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(N3)," ",REPT(" ",99)),(1+(LEN(N3)-LEN(SUBSTITUTE(N3," ",""))>2)+(LEN(N3)-LEN(SUBSTITUTE(N3," ",""))>4))*99))&","&N3,LEN(N3)))

the above formula is this way

srinivas shinda singh will come Output- SINGH,SRINIVAS SHINDA upto 3 words will take last single word reverse

srinivas shinda singh kumar Output -SINGH KUMAR,SRINIVAS SHINDA upto 4 to 6 words need to take 2 last words reverse

srinivas shinda singh kumar sons Output-SINGH KUMAR SONS,SRINIVAS SHINDA SINGH above 6 words need to take last 3 words reverse .

this all i am getting in above formula need to get if this single character comes with dot.

Srinivas m.t. OUTPUT T,SRINIVAS M.

Srinivas m.t.i. OUTPUT T.I,SRINIVAS M.

srinivas m.t.i.l.k. OUTPUT I.L.K,SRINIVAS M.T.

need to edit the above formula and add this changes to above formula i need all the out put in one formula

 

[bosco_yip]

Modified facethegod's formula

=UPPER(MID(TRIM(SUBSTITUTE(A1,".",". "))&","&TRIM(SUBSTITUTE(A1,".",". ")),FIND("$",SUBSTITUTE(TRIM(SUBSTITUTE(A1,".",". "))," ","$",LOOKUP(LEN(TRIM(SUBSTITUTE(A1,".",". ")))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1,{0,4,6},(LEN(TRIM(SUBSTITUTE(A1,".",". ")))-LEN(SUBSTITUTE(SUBSTITUTE(A1,".",". ")," ","")))-{0,1,2})))+1,LEN(TRIM(SUBSTITUTE(A1,".",". ")))))

 

[shrinivasmj]

thanks a lot for the update its works but

input is
[TABLE="width: 349"]
<colgroup><col width="349"></colgroup><tbody>[TR]
[TD="class: xl65, width: 349"]srinivas m.j.l.m[/TD]
[/TR]
</tbody>[/TABLE]

Out put is
[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 186"]
<colgroup><col width="186"></colgroup><tbody>[TR]
[TD="width: 186"]L. M,SRINIVAS M. J.

i need in this format
[TABLE="width: 186"]
<colgroup><col width="186"></colgroup><tbody>[TR]
[TD="width: 186"]L.M,SRINIVAS M.J.

if there is no space in name after dot in input , the output also not contain space after dot
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[shrinivasmj]

it working thanks a lot i need it badly, but need some changes

*.input srinivas , out put is blank . its not taking single word.

*.input srinivas m.j.k ,out put J. K,SRINIVAS M. , if the input is without space after dot. then the out should come with out space as --J.K,SRINIVAS M.

please help me need as soon as possible

 

[shrinivasmj]

Waiting for replay need the formula urgently