How to Find a Mid Name from Given Names


I am trying to get the middle Name from below mentioned Name by using Excel Formula-

Ram Kumar Singh

Manish Kumar Rana

Danish tomar Singh

Kindly note that the formula should be dynamic so that when we drag the form till last row we should be able to get the Middle Name.


  Devbrat Tripathi

=MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1)

  Anuj Kumar       28 Aug 2017       0       0     

Hello Sir,

Good Evening!

I am able to solve below problem in below way. Please have a look:

First I have separated (first+middle) from full name using left function.

Then I have separated middle name using right function.

First+Middle=LEFT(B2,FIND(" ",B2,FIND(" ",B2,1)+1)-1)

Middle: =RIGHT(D2,LEN(D2)-FIND(" ",D2,1))

It looks like below:

Name Left+Middle Middle
Ram Kumar Singh Ram Kumar Kumar
Manish Kumar Rana Manish Kumar Kumar
Danish Tomar Singh Danish Tomar Tomar
Shubhadeep Kumar Manna Shubhadeep Kumar




  Debapriya Bej       28 Aug 2017       0       0     

Post Your Answers:

Please use the CODE HIGHLIGHT Button to format/highlight your codes if any