Adding Collaborator initials in a formulas

Ask the community Formulas
#1

I want to use a formula to add the Collaborations initials to the Event name so I know who is going just by looking at the primary field name in the calendar view but I am unclear how to accomplish this.

1 Like
#2

It’ll be pretty easy to get the first character of a collaborator’s first name, but it will take some cleverness to get the first character of the last name - so someone else might be able to improve upon my solution.

First Character of First Name
Formula
LEFT({collaborator},1)

First Character of Last Name
How it works
(1) find the position of the first space character (2) get the character immediately after it
Limitations
If there are multiple spaces in the collaborator’s name, you may get the wrong result. For example, “Bob Jones” would correctly return ‘BJ,’ but so would “Bobby Jones Anderson” even though Jones is Bobby’s middle name.
Formula
LEFT( FIND(" ",{collaborator}) + 1, 1)

Combined Formula
LEFT({collaborator},1)&LEFT( FIND(" ",{collaborator}) + 1, 1)

2 Likes
#3

Very cool! Thanks for the solution.

#4

I tried the combines formula and got an error. I then tried the two halves separately and The first half works but the second does not.

Any ideas?

1 Like
#5

Just realized another issue in case you have a solution. I have events with more than one Collaborator in the Collaborator field (Multi-select) but your formula (the first half that is working) only finds the first initial of the first one. I expected it would apply the formula to each selection in the collaborator field.Do I need some kind of rollup field to get both?

#6

I expected it would apply the formula to each selection in the collaborator field

Afraid not. Fair warning, Airtable has a lot of formula limitations for comma separated data. But the above is pretty simple. It just takes a string of text (whether that’s “John Williams” or “John, Peter, Paul” and returns the first character combined with the first character after a space. That’s it. It has not idea what a first or last name is or whether data is comma separated or not.

Can’t know what’s causing the error without seeing the formula you inputed and a list of your column names. Formulas can be pretty picky about syntax. The above code ran fine in my test table. You likely just need to rename the column names I’m using inside the curly braces.

#7

All in all, I don’t expect you’re going to have any easy solve for this issue if the data isn’t:

  • only one name per record
  • no additional space characters besides the one between the first and last name

More effective solutions (that wouldn’t have these dumb formula limitations) would involve third party tools (either through integrations or the API). But before going down that road, it might be worth really asking yourself how important having name abbreviations is.

#8

It is WAY beyond my ability to actually create a formula to do this but I can conceptualize a way make this happen by using a combination of If & finding the commas and extracting information after the comma but I think your last comment convinces me having this is not worth the effort unless someone want to do it as an exercise.

Thanks again for your help & efforts.

#9

Sure thing. And…

I can conceptualize a way make this happen by using a combination of If & finding the commas

…you’re right. A lot of languages can handle that really well. But Airtable’s formulas aren’t mature enough yet. By using third party tools, people are able to use whatever languages they like. But that also means setting up and maintaining that additional tool.

#10

Try this:

UPPER(LEFT({collaborator},1)&LEFT(TRIM(MID(SUBSTITUTE({collaborator}," “,REPT(” ",LEN({collaborator}))), (2-1)*LEN({collaborator})+1, LEN({collaborator}))),1))

#11

Try out the MID function for the second initial in Name (example: “Eric Koston”):

LEFT(Name,1) & MID(Name,FIND(" ",Name)+1,1)

Returns: “EK”

1 Like