Shiftting of data

**avk** · 12-10-2009, 06:47 AM

Hellow friends
i have one excel sheet1. In sheet1 no.of records near about 5000 to 6000 store.

In column b 2 to b 6000 input name as per attach file. We have type some 10 customer name for example.

Now i required, to show first surname for example in b2 column the name is : Ashish gandhi
i want to display firstly surname : gandhi shish or in b10 column name is : J.p. Singh
i want to display firstly surname :singh j.p..
In above matter can u tell me is their any trick or any formula to change as per above.
Enclosed file your ready ref.

**zbor** · 12-10-2009, 06:55 AM

How do you want to display MEHUL KRISHNA RATHOR and MUKHESH S. MEHER?

**teylyn** · 12-10-2009, 06:58 AM

Hi,

this will flip last name and what's before last name - put this in G2 and copy down

=RIGHT(B2,LEN(B2)-FIND("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))&" "&SUBSTITUTE(B2,RIGHT(B2,LEN(B2)-FIND("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),"")

hth