Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Adding Collaborator initials in a formulas

Topic Labels: Formulas
1760 10
Showing results for 
Search instead for 
Did you mean: 

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.

10 Replies 10

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

First Character of Last Name
How it works
(1) find the position of the first space character (2) get the character immediately after it
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.
LEFT( FIND(" ",{collaborator}) + 1, 1)

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

Very cool! Thanks for the solution.

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?

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?

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.

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.

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.

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.

Try this:

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

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”