[Solved] Sorting by middle name

[Upper West Sider]

My data are organized as follows: first name_middle name_last name

Assume the sample name is entered in cell A1. I have the formula for listing the first name in cell B1: =LEFT(A1,SEARCH("_",A1)-1)

QUESTION: What is the formula for listing the middle name in cell C1?

Thank you.

 

[Seemanto Saha]

Hello Upper West Sider,

Thanks for sharing your problem with us. I understand that you want to extract the middle name from a full name. For this purpose, first, I used your mentioned formula in Cell B2 to extract the first name from Cell A2. I will use this first name output to extract the middle name.​

=LEFT(A2,SEARCH("_",A2)-1)

​

Afterward, I used the following formula in Cell C2 to extract the middle name.

=MID(A2,LEN(B2)+2,SEARCH("_",A2,LEN(B2)+2)-1-LEN(B2)-1)

​

Here, I used the MID function to extract the string from 2 characters after the first name to the next delimiter (“_”) character.​

If you require extracting the last name also, you can apply the following formula in Cell D2.

=RIGHT(A2,LEN(A2)-2-LEN(B2)-LEN(C2))

​

To learn more about separating first name, middle name, and last name from full name, navigate the following article:
Separate First Name Middle Name and Last Name in Excel

I hope this solution resolves your problem. Let us know your feedback.

Regards,
Seemanto Saha
ExcelDemy

 

[Upper West Sider]

Hello Upper West Sider,

Thanks for sharing your problem with us. I understand that you want to extract the middle name from a full name. For this purpose, first, I used your mentioned formula in Cell B2 to extract the first name from Cell A2. I will use this first name output to extract the middle name.​

=LEFT(A2,SEARCH("_",A2)-1)

​

Afterward, I used the following formula in Cell C2 to extract the middle name.

=MID(A2,LEN(B2)+2,SEARCH("_",A2,LEN(B2)+2)-1-LEN(B2)-1)

​

Here, I used the MID function to extract the string from 2 characters after the first name to the next delimiter (“_”) character.​

If you require extracting the last name also, you can apply the following formula in Cell D2.

=RIGHT(A2,LEN(A2)-2-LEN(B2)-LEN(C2))

​

To learn more about separating first name, middle name, and last name from full name, navigate the following article:
Separate First Name Middle Name and Last Name in Excel

I hope this solution resolves your problem. Let us know your feedback.

Regards,
Seemanto Saha
ExcelDemy

Thank you. This works very well. I much appreciate your assistance!

Anthony Jiga