• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HOW TO BRING INTIAL TO LAST

webmax

Member
Hi

I have the Following Name
Input

1) M.S. Dhoni
2) S. John

I would Like the Output and intial should Come Last of the Name using formula

Output
Dhoni MS
John S

Regards
Shahul
 
Hope this is what you are after


What if name is ram kumar sharma.

below types will work for all instances.


Either

=UPPER(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&" "&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))))

OR

=UPPER(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&" "&MID(A1,1,FIND(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),A1)-1))
 
If you want output as "S. DHONI" wrap my formula in =Upper as Khalid mentioned or if you want your output as "Dhoni S." wrap it in =proper...
 
Hi

I tried using the proper formula but the Output is showing as S. DHONI

I am attaching the sheet for your reference.
 

Attachments

  • Intial change.xlsx
    8.8 KB · Views: 2
I could not make out what you are actually looking for , please give the actual data and expected result for each name in a spread sheet without any formula.
 
Hi
Again if the Name is Dhoni S it is showing as S Dhoni

I am attaching the file for your reference.
 

Attachments

  • Intial change.xlsx
    8.6 KB · Views: 6
=UPPER(IF(LEN(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99)))>2,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))&" "&LEFT(A2,LEN(A2)-LEN(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99)))),A2))
 
Dear Webmax,

Your output in not consistent thoroughly. How a formula can identify that MS Dhoni should be change to Dhoni MS while Dhoni S should remain Dhoni S.
 
Dear Webmax,

Your output in not consistent thoroughly. How a formula can identify that MS Dhoni should be change to Dhoni MS while Dhoni S should remain Dhoni S.
Dear Faseeh,

I want the Intitial to be Come in the Last of the Name. That is my requirement.
 
Back
Top