To get only the first name from a full name written in “Last name, First name” format, the formula which employs RIGHT, LEN and FIND functions can be used to achieve this. In the general formula, as shown above, the name refers to the full name that is written in this format:
To separate the last name from the first name, a comma and space are used.
How the Excel Get the first name from a name with comma formula works
With a high level of function, the Excel Get the first name from the name with comma formula utilize the function RIGHT to remove characters from the right side of a full name. In order to figure out how many characters needs to be removed to get only the first name, the Excel Get the first name from the name with comma formula employs the ‘FIND’ function to locate the exact position of the comma “, ” in the full name:
FIND(“,”,B7) (this finds the position of comma in B7
Once this is done, the FIND function then returns the position where the comma and space are as a number, and this is finally used to subtract the number gathered from the total length of the full name:
LEN(B7)-FIND(“,”,B7) (this function counts the length of the first name + 1
The result from this function is the total length of the first name and an additional character which in this case is the comma. To obtain the right length of characters, 1 is then subtracted:
LEN(B7)-FIND(“, “,B7)-1 (this count and record the length of only the first name
Since the name is arranged in a reversed order (LAST NAME, FIRST NAME), the RIGHT function can then be applied to remove the length of the first name as calculated before.
As seen in the example above, the name is “Bukky, Churchill”, and so, the position of comma falls at position 6. From this number inference the underlying formula is broken down to this:
10 – 6 – 1 = 3 (this is used to get the length of only the first name)
RIGHT(“Bukky, Churchill”,3) (finally interpreted thus to return “Churchill” as the first name)
Figure 1: Example showing how to Get first name “Churchill” from the name with comma “Bukky, Churchill”
The formula used is:
In the above example, the active cell C7 contains the first name “Churchill”
- The Excel Get the first name from a name with comma formula is designed to work only with names that are written in Last name, First name format, and separated using a comma then space.