If formula to split first & last names from full

Trying to seperate first name and last name without errors coming up when there is no data. e.g. Contact Full Name is ‘John Smith’, into seperate columns First ‘John’ and Last ‘Smith’

could someone please help me with the following formula for Last Name? it works, but i still get an error (One of the parameters is invalid) when there is no data in Contact Full Name

IF(LEN({Contact Full Name})>0,RIGHT({Contact Full Name}, LEN({Contact Full Name})- SEARCH(" “, {Contact Full Name}),” "))

Hey Caprice!

Charlotte here for the team! :wave: Happy to help out with this!

Just to confirm, you’re using the formula you wrote above and it returns the first name correctly but it is returning an error when there is no data in the “Contact Full Name” field? And you also need a formula for the last name?

If you are looking to have the first and last names in two separate columns, I’d suggest using the following formulas:

First name:

LEFT({Name},SEARCH(" ",{Name},1))

For the last name:

RIGHT({Name},LEN({Name})-SEARCH(" ",{Name},1))

These will be two separate formula columns for the two separate parts of the name.

For your IF formula to show a value when there is no data to use, you will want to set up a false condition at the end of the formula. The false condition will show up whenever the preceding condition is not met/not true. So in this case, when there is no data in the column to use, it should return the false condition.

Here’s an example: IF({Status}=“Done” ,1,0) - in bold, we have our condition, followed by what should be displayed if the result is true (so if Status is indeed Done it will return 1), and then followed by a “false” condition, the 0.

You will want to set up something like “N/A” or “No info” at the end of your formula so that it returns a value when there is no value instead of an error.

Does this help? Let me know what you think!

1 Like