For Extracting second name from full name, we can use “Right”, “Find” & “Len” formulas. Let’s understand each of the formula before going further:
a)RIGHT: This function displays a specified number of characters from the right hand side of a piece of text
Syntax: =RIGHT (Original Text, NumberOfCharactersRequired)
b) FIND: This function looks for a specified letter inside another piece of text. When the letter is found the position is shown as a number. If the text contains more than one reference to the letter, the first occurrence is used. If the letter is not found in the text, the result #VALUE is shown.
Syntax: =FIND (LetterToLookFor, TextToLookInside, StartPosition)
c) LEN: This function counts the number of characters, including spaces and numbers, in a piece of text.
Syntax: =Len (Text)
Now we have learned about Right, Find and Len. Now let’s use them to find out Second names from the Full Name
So, what exactly we have done?
a)FIND (“ ”,C29)—It finds the Position of the Space
b)LEN (C29)-FIND (“ ”,C29)—This is the length of the second name. Calculated by taking overall length of the complete name and subtracting the position of the space
c)RIGHT (C29,LEN (C29)-FIND (“ ”,C29))—This is the second name. Calculated by using the RIGHT () Function to extract the rightmost characters upto the length of the second name.
Hope now you are more confident with your excel skills and share the post with your friends.