How to separate first names and last names in the most correct way?

Question

Hey friendsI have a column of customer full names.I would like to separate this column into 2 separate columns - one for first names and the other for last names.I used the following formula (screenshot attached) to separate the first and last names, but there is a problem:If someone entered only their first name without a last name, the column remains empty and I don't understand why.I would be very happy for your help in this matter!Thank you

Andrey_Kovalev · Accepted Answer

@zerem , try IF(FIND(" ",{Full Name})=0, {Full Name}, LEFT({Full Name}, FIND(" ",{Full Name})-1))

kuovonne · Answer

If someone entered only their first name without a last name, the column remains empty and I don't understand why.

This is because the FIND() function returns zero if it doesn't find a space, which is the case when there is only a first name. And zero minus one is negative one. SO the LEFT() function returns negative one characters, which is no characters.

There are many possible functions to extract the first name. Another possible function would be
REGEX_REPLACE({Full name}, " .*", "")
This formula replaces the first space and any following characters with an empty string, leaving only the first name.

Another thing to consider is to see if you can get the source of the data to enter the first name and last name separately. Having data entry correctly to begin with saves a lot of work in cleaning the data later. When you end up with people typing in middle names, having multi-word last names, or suffixes, cleaning up names gets tricky.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded