Split first and last (but working when last is missing)

Platform discussions
1

I’m using the following formula:
LEFT({Name},SEARCH(" ",{Name},1))

But, when a column doesn’t contain a first AND last name, it brings up an error. I’m hoping to get some help in a formula that will work to show the first name, in all cases.

2

Hi Jason,

Since SEARCH(" ",{Name},1) returns an error if {Name} doesn’t contain a space (seems like a bug to me)… you’ll need to use another method to figure out if there is a space or not in {Name}, then use an IF statement to handle both scenarios.

A classic method is to compare the number of characters of the text with and without spaces. Here is how you do it.

LEN({Name})-LEN(SUBSTITUTE({Name}," ",""))

If the length remains the same after removing the spaces (ie. the calc above =0), it means there aren’t any. In this case, we want the formula to return {Name}. To handle the other case, we can use your formula.

IF(LEN({Name})-LEN(SUBSTITUTE({Name}," ",""))=0,{Name},LEFT({Name},SEARCH(" ",{Name},1)))

Want to get rid of the formula column and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com

1 Like