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:
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:
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?
In the Split column, I used the following formula:
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:
And in the Last column, I similarly have:
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.
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?