Extract Initials for Given Names

i would maybe separate the names into individual fields, first, middle, last, then write a formula that returns the first letter of each and concatenates them. separating names in individual fields can have many benefits in preparing documents and comparing data or finding data that was put in wrong even. being able to isolate and filter so far as to one word has been useful for audit purposes and combining duplicate info. if you prefer to see the long form of the name you can write a formula that concatenates them for your reference

@David_Grove The reason that this is such a dilemma is because, unlike other programming languages, Airtable formulas are very simplistic and don’t give you any way to iterate through a string of text to break it apart.

So, as @Rebecca_Elam mentioned, the best long-term solution is to break apart your name field into separate fields. Ideally, as Rebecca said, you’ll want to create 3 text fields for this: “First Name”, “Middle Name”, and “Last Name”. This would probably be your best long-term solution for all sorts of things, including concatenating the initials that you’re looking for. So Rebecca’s solution is the way that I would go.

Alternatively, you could create 3 formula fields for this as well. There is only one known way of separating a single name field and dividing it into 3 different formula fields, and @Justin_Barrett is the one who figured it out here:

Also, If you know JavaScript, you could likely write a custom JavaScript which divides your name field into 3 separate text fields (instead of formula fields). That would be another way to go.

You guys are killing me (and the customer). In many cases, people who manage data in Airtable rarely have control over the data sources that flow into their bases. Suggesting that they completely change the data model to accommodate a simple transformation requirement probably doesn’t resonate with folks who want to feel in control of their data.

This is yet another example of Airtable failing to do some very simple stuff in string management, a topic I used recently to stir up some dust with @Taylor_Savage recently using this (and 2400+ other community posts like it over the past five years). :winking_face:

There is certainly a native formula recipe that does this using LEFT(), MID, and FIND() and RIGHT(), but it would require an hour or more to develop it and when complete, you’d need alcohol to recover. I stopped building complex formulas to overcome this issue, but you can read about some of my insane undertakings here, and here. And this post might actually help. And this is a little insane.

@David_Grove, you should hold out for help from someone in the community to carve up these values to get the initials you expect - it is possible. This is also very easy to do in Script Blok - perhaps @kuovonne already has an example she can share. (I love making new tasks for very smart people.)

@Taylor_Savage, you know that the proposed SPLIT() function would do this elegantly in about 1 minute, right? :slightly_smiling_face:

To all readers - the following formula is NOT supported by Airtable; it is simply an example of a proposed SPLIT() feature used to address this user’s challenge.

IF( {fieldName}.Split(" ").length = 2, {fieldName}.Split(" ")[0][1] + "." + {fieldName}.Split(" ")[1][1] + ".", {fieldName}.Split(" ")[0][1] + "." + {fieldName}.Split(" ")[1][1] + "." + {fieldName}.Split(" ")[2][1] + ".")

@David_Grove Welcome to the Airtable community!
If you are interested in purchasing a formula or a script for this, please let me know.

@Bill.French How did you know that I wouldn’t be able to resist this little puzzle, even though I am swamped with work?

Here is a base with the formula

Here is a form where you can test it out

Comments:

• This formula can create initials for a maximum of three names, and there must be exactly one space between each name.
• A script could handle more than three names, multiple spaces, and other irregularities. However, a script has to be triggered by either a button or an automation.

When it comes to coding (and I consider complex formulas like these to be coding), there are many different methods. Bill suggested using one set of functions. Justin’s formulas use a different set of functions. I happened to use yet a different set of functions that works for me.

I recommend using whatever method you understand best so that the code will be easier for you to maintain.

This is an interesting proposal. While I’m all for a split() function, I think this syntax is problematic, as it doesn’t handle arbitrary lengths well. I would rather have a split() function that works in conjunction with other array functions, plus a map() function.

I don’t know you per-se, but I know how smart coder’s brains function.

In case anyone tried my links last night, I apologize if you had trouble viewing the results from submitting into the form. I setup automations to delete form submissions after several minutes. However, I initially set the delay to 1 second to test the automation and forgot to reset it to a longer interval. I have since corrected the automation to have a more reasonable delay.

Indeed, but intended to demonstrate only the context of the requirements and to contrast the simplicity as compared to a formula without SPLIT().

I’m simply putting a finer point on the need for better string handling and pervasive transformations that almost every user needs.

Well said. However, the formula isn’t visible in your base. In the upper-right corner, I see “Sign Up” instead of “Copy Base”, so the formula can’t be seen.

Also, my admittedly-long test name didn’t fully parse.

Once again demonstrating that formulas have their limitations. For a case like this, I’d be pretty tempted to turn it into an automation, which could work with names of any length, including crazy long names like the one I made up.

@Justin_Barrett Thank’s for testing out my demo.

Yes, your long test name didn’t parse the initials beyond the first three. In my comments in my original post, I explicitly stated that this particular formula only worked for three names, along with other limitations of formulas.

As for not sharing the formula (and thus not sharing the base), I am still working on balancing giving away formulas for free, versus my need for income.

Oops! I think I glossed over those comments before testing. Sorry about that.

I completely understand. I was just confused when you said “Here is a base with the formula,” which gave me the impression that you were sharing the formula along with the base.

Thank you all, for your helpful responses.

Initially, before posting, I had considered Rebecca_Elam’s idea. However, I didn’t know how to deal with an unknown number of names (and I am just a private user creating my own personal sheet music library with the free version [hence no blocks]).

There are composers with more than three given names. The longest of which I am currently aware has 30 names. (Named after each member of his father’s orchestra.) Admittedly, that is absolutely off-the-charts, most unusual case. But, it illustrates the point that I didn’t want to have a built-in limit.

But, since I don’t have scripting (I’m thinking I need blocks for that, am I incorrect?), I decided to go with a maximum of two initials (I’m just leaving Bach’s son, and others with more than two given names “in the dust” and only including the first two initials) by having three fields: first name, middle names, and last name, and using the first initial of the first name and middle names fields.

I’ll use that until I find (from actual experience) that it is a showstopper!

Thank you again.

Now, on to figure out how to make a user interface (like a form) to permit entry by user of search parameters (that is values to use in a filter [i.e. WHERE clause]) for a dynamic query. However, the more I look at Airtable, the more I think it’s database capabilities (I read that as SQL) are fairly limited.

Anyway, that’s a topic for another thread, which I will likely post, later.

DG

Scripting block is free for all users until March 2021. (Originally you would need a pro subscription after this month, but free use has been extended.)

Sounds like a great, simple solution! Thanks for reporting back.