Split Text Using Formula with Conditions

Platform discussions
1

I found a great article on splitting text that is working great for splitting first and last name ( Split Text using a formula - Platform discussions - monday Community Forum) however I am trying to solve for if another column is filled out with preferred name, use that one instead. My preferred name column is however used for Preferred name and pronouns so I would still need to identify the first name in the column. Here is my current formula that I am getting an error on:

IF({Preferred Name/Pronouns}=" “,(LEFT({Name},SEARCH(” “, {Name})-1)),(LEFT({Preferred Name/Pronouns},SEARCH(” ", {Preferred Name/Pronouns})-1)))

or

IF({Preferred Name/Pronouns}=“”,LEFT({Name},SEARCH(" “, {Name})-1),LEFT({Preferred Name/Pronouns},SEARCH(” ", {Preferred Name/Pronouns})-1))

2

Hey Cedar,

I am going to reach out to our expert formula gurus internally, incase they have any advice here :+1:

In the meantime, I wonder if our community expert @JCorrell has any insight? :pray:

3

@cfrancis-scofield, @BiancaT,

The primary issue with the formula is that the SEARCH() function MUST find the parameter that it is searching for to complete without error. So, we can make it work by adding a space to the end of the column value we are searching.

Give this a try:

IF({Preferred Name/Pronouns} = "", 
   LEFT({Name}, SEARCH(" ",{Name} & " ") - 1), 
   LEFT({Preferred Name/Pronouns}, 
      SEARCH(" ", {Preferred Name/Pronouns} & " ") - 1))

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

1 Like
4

Jim. You are my hero! I hope to be a Monday.com expert like you some day. You have solved every question I have posted! This worked perfectly! Thank you again!

1 Like
5

Cedar is right - You really are the monday HERO!!! Thank you Jim :raised_hands: :raised_hands: