Help

Re: IF Field Contains User(s) then Replace User Name with Initials Help

Solved
Jump to Solution
1086 0
cancel
Showing results for 
Search instead for 
Did you mean: 
jRich_Audio
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi All,

I have a Calendar table within a base that we use to assign calendar events to Airtable users. We also share the calendar link with everyone so the calendar will populate on their iPhone calendar and such.  Because of this, we want to show what event is assigned to which user in the calendar event ID (labeled "Event" in the screenshot below).  We currently do this with a concatenate formula but it will show each users full name, which makes the Event ID very long.  Current Event formula is as follows and works fine:

 

CONCATENATE(IF({Tentative?}=1,"(t) ",""),Project, " ",{Service Call}," ", RMA," ",Title, " ", {User (Assigned To)})

In short, I can not find a way in which to swap out each users name for their initials in the form of custom text (not just taking the first and last initial from the users name since two users have the same initials "JR").  I am looking for some sort of formula to insert into my existing {User (Assigned To)} spot in the formula above that is like this in english:

IF one, any, or all user(s) populate in the {User (Assigned To)} field then replace their user name with "this text".

 

So in the screen shot below, the first cell currently displays as:

(t) NWF-0450 Pre-wire/Pre-rig James Richardson, Joe Hilling, Jordan Roberts

But I would like for it to display as:

(t) NWF-0450 Pre-wire/Pre-rig (JR, JH, JSR)

I have tried various versions of the IF, SWITCH, FIND, and other formulas but can't get the result I am looking for.

 

Any help is appreciated!!

Screen Shot 2022-12-15 at 9.55.56 AM.png

 

 

3 Solutions

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

How many potential users are there? If there are only a handful of people, you could use nested SUBSTITUTE functions. 

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

  {Users},
  "James Richardson",  "JR"),

  "Joe Hilling", "JH"),

 "Jordan Roberts", "JSR")

See Solution in Thread

jRich_Audio
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks Kuovonne!!!  I put that into the main event ID as follow and it does exactly what we need!!

 

CONCATENATE(IF({Tentative?}=1, "(t) ",""), Project, " ",{Service Call}," ", RMA," ",Title, " (", CONCATENATE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{User (Assigned To)},
"James Richardson", "JR"),
"Joe Hilling", "JH"),
"Jordan Roberts", "JSR"),
"Randolph Prondzinski", "RDP"),
"Brandon Young", "BY")), ")")

See Solution in Thread

jRich_Audio
5 - Automation Enthusiast
5 - Automation Enthusiast

Here is a screenshot of the solution.

Screen Shot 2022-12-16 at 8.58.13 AM.png

See Solution in Thread

3 Replies 3
kuovonne
18 - Pluto
18 - Pluto

How many potential users are there? If there are only a handful of people, you could use nested SUBSTITUTE functions. 

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

  {Users},
  "James Richardson",  "JR"),

  "Joe Hilling", "JH"),

 "Jordan Roberts", "JSR")

jRich_Audio
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks Kuovonne!!!  I put that into the main event ID as follow and it does exactly what we need!!

 

CONCATENATE(IF({Tentative?}=1, "(t) ",""), Project, " ",{Service Call}," ", RMA," ",Title, " (", CONCATENATE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{User (Assigned To)},
"James Richardson", "JR"),
"Joe Hilling", "JH"),
"Jordan Roberts", "JSR"),
"Randolph Prondzinski", "RDP"),
"Brandon Young", "BY")), ")")
jRich_Audio
5 - Automation Enthusiast
5 - Automation Enthusiast

Here is a screenshot of the solution.

Screen Shot 2022-12-16 at 8.58.13 AM.png