First name, Last Name Split in first column

Platform discussions Special workflows & use cases
1

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.

2

@JCorrell Any ideas =P

3

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

4

@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:
image

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

5

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

6

Just posting to bump.

7

@Mrjoelbaker

Which is the portion of the name you want inored?

8

In the example it would be Rico

@JCorrell

9

@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

10

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.