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

[jRich_Audio]

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!!

 

 

[kuovonne · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

Here is a screenshot of the solution.