Parsing First/Last Name Issue

I’m using the Left and Right formulas to parse first and last names from a full name column. The Left formula is working perfectly:

=Left(Name,Find(" ",Name)-1)

With the name Abby Malvestuto, that returns, correctly, “Abby.” However, when I use the Right formula for the last name, some rows are correct and some are wrong. The formula is:

=Right(Name,Find(" ",Name)+1)

With the same name as above, this formula returns “estuto,” cutting off half the name. With the name Aaron Barrett, however, the formula correctly returns “Barrett.”

It seems about 50/50 whether it will return the correct name or not. Most of the time it’s cutting off just the first letter of the last name, but there are other times when it cuts off several, as in the example above. It also doesn’t seem to have anything to do with the length of the name, since some long names are working and some short names are not. Is this a bug?

I have verified that the full name is correctly entered, and that there are no trailing or extra spaces. Anyone have any ideas?

Okay, I found the problem. The Right formula, unfortunately, just isn’t going to work here. Anyone have any ideas on the best way to get the first and last name here?

One approach may be using the Split (https://coda.io/formulas#Split) formula. I setup my sample table like this:

In the Split column, I used the following formula:

image

This formula looks for a particular character (in this case a space, which is what is between the quotation marks), and splits the items into a list whenever it sees that character.

In the First column, I then have:

image

And in the Last column, I similarly have:

image

These formulas grab the first and last parts of a list respectively.

A major drawback to this approach is if someone has a space in their first or last name, but that can be worked around. There is definitely room to optimize this approach, but hopefully this gets you headed in the Right direction.

1 Like

This worked! I think there needs to be something a little cleaner than this, but I appreciate your help.

Dear @Joseph_Sorensen and @Joseph_B

I assume that the below will be of your interest;

Amicable,
Jean Pierre

4 Likes

Here’s a way to handle people with multiple first names:

7 Likes

I love the use of negative numbers in Slice()

That’s a cool trick and it never hit me to give that a try. I’m on the lookout for things that need counted backwards now, haha!

1 Like

What about dealing with the case of the names not all being in Title Case, how can we convert the first letter of each name to upper()? I’m thinking I could do it if I chained like 10 formulas but maybe there’s a more simple way to capitalize / capital case?

Dear @Johg_Ananda

I would like to refer you to this post, with credits to @Asaf_Dafna

@Jean_Pierre_Traets thanks! that’s actually a post I wrote after finding nothing on the board (this thread was part of my search). Thanks for helping!

1 Like

I use this formula in the column where I want the First Name to appear, pulling from the column with the Full Name

First(Split(thisRow.[Full Name]," "))

input = John Doe —> output = John