![]() In case you have the first, middle, and last name, the formula becomes a little bit longer.īelow I have a data set where I have the first, middle, last name in column A, and I want to extract the last name from it. Extract Last Name Using Formulas (When you Have First, Middle, and Last name) Using the TRIM function makes sure that all these extra spaces are ignored and only one space character between the first name and the last name is considered. This is because there is a possibility that the cells containing the names may have leading, trailing, or double spaces in them. Note that I have used TRIM(A2) instead of A2 in this formula. This tells us how many characters are there after the space character in the name.Īnd this value is then used in the RIGHT formula to extract the last name from the full name. Since we have the position of the space character, we can subtract that value from the length of the name (which is given by the LEN function). Since the position of the space character would be between the first and the last name, once we have the position of the space character, we can use that to extract everything to the right of it (which would be the last name)īut how many characters from the right should it extract? The above formula uses the FIND function to get the position of the space character in the name. Let me quickly explain how this formula works. Suppose you have a data set as shown below where you have the first name and the last name in column A, and you only want to extract the last name from it. To make sure there are no trailing spaces, you can use the TRIM formula (read – how to remove trailing spaces in Excel) Extract Last Name Using Formulas (When you Have Only First and Last name) In case there is a space character after the last name, doing the above Find and Replace operation would remove everything from the cell. Note: For this method to work you need to make sure that there are no trailing spaces in your data. So when I ask it to find an asterisk followed by the space character, it will find the last space character in the cell and everything before that space character would be considered a part of the asterisk.Īnd when I replace it with a blank, everything before the last space character is removed. In the ‘Find what’ field in the Find and Replace dialog box, I have used an asterisk sign (*) followed by a space character.Īsterisk is a wild card character that represents any number of characters in Excel. The only condition is that the last name should be at the end of the name For example, if you have names that may or may not have a middle name/initial, or may have a prefix such as Mr or Ms. The above method would also work in case you have an inconsistent names dataset. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |