First name, Last Name Split in first column

[Mrjoelbaker (Joel baker)]

I got a probably easy fix but i’m not great with Monday code.

I have the first column i want to dissect as : Doe, Jane

I have these formulas to split them into first name / Last name columns

First name: RIGHT({Name}, LEN({Name})- SEARCH(" ", {Name}))

Last name: IF(LEN({Name})>0,LEFT({Name}, SEARCH(" “, {Name}&” “)-2),”")

It works perfect if entered correctly. But, when someone enters with 0 spaces ( Jane,doe) it messes up my first and last name column to: First name ! Last name Jane,do

Any fixes for if they forget the space after the comma?

Also, sometimes we have automations that put (copy) behind the name, how do we get rid of that as well.

[Mrjoelbaker (Joel baker)]

@JCorrell Any ideas =P

[Mrjoelbaker (Joel baker)]

I have the formula TRIM(SUBSTITUTE({Name},“(copy)”," ")) to get rid of the copy im just not sure how to add it to the formulas

[JCorrell (Jim - The Monday Man)]

@Mrjoelbaker

Try this for last name:
REGEXREPLACE(SUBSTITUTE({Name}," \(copy\)", ""), "(.*),\s*(.*)", "$1")

… for first:
REGEXREPLACE(SUBSTITUTE({Name}," \(copy\)", ""), "(.*),\s*(.*)", "$2")

---

BTW, you can use this Column Magic recipe to remove the “(copy)” from copied items:

---

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

[Mrjoelbaker (Joel baker)]

Those worked perfectly, theres only one hiccup i didnt think of

Rico Romero, Jenna (item name)

If the user had 3 names how can i get the formula to ignore the middle name

the example shows as middle last, first

@JCorrell

[Mrjoelbaker (Joel baker)]

Just posting to bump.

[JCorrell (Jim - The Monday Man)]

@Mrjoelbaker

Which is the portion of the name you want inored?

[Mrjoelbaker (Joel baker)]

In the example it would be Rico

@JCorrell

[JCorrell (Jim - The Monday Man)]

@Mrjoelbaker

Change last name formula to:
REGEXREPLACE(SUBSTITUTE({Name}," \(copy\)", ""), "(?:(?:\w*\s+)*(\w+),.*)", "$1")

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

[Mrjoelbaker (Joel baker)]

@JCorrell

REGEXREPLACE(SUBSTITUTE({Name}," (copy)", “”), “(?:(?:\w*\s+)(\w+),.)”, “$1”)

That works perfect, the only problem now is if the person is like allen Jr, Tom it will bring over the Jr instead of Allen.

It does however work for the 3 named people like I above mentioned.

[myrrhw (Myrrh Winston)]

I understand that if one uses a formula-generated FirstNameLastName column, then it can’t be used in automations. Fillout allows you to concatenate within the Forms view, so that it creates an item that can be used in automations. Maybe monday can add this to its Forms…