vuekvm.blogg.se

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.

This can be done by wrapping the function in Proper.The above method would also work in case you have an inconsistent names dataset.

Source: Change the last word to titlecase in Google Sheets This works to parse the last word in excel =MID(SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,256)īy the way, if you want the first word in a cell? Change “RIGHT” to “LEFT” in the formula. Or put a whole column of phrases into the A column, setup the function in the B column and drag it down from the bottom right corner.ĭrag down and you have your last word from the phrase parsed into column B. To use it, put the the phrase you want to parse for the last work in cell A1 and the function above in cell B1 and hit enter. =TRIM( RIGHT( SUBSTITUTE( A1 " " REPT( " " 100 ) ) 100 ) )īut if you might have any trailing spaces in your cell use this to get the last word. Here’s what you need to retrieve the last word (or the first) in Google Sheets cells or using Excel if that’s your preference.