Extract Initials for Given Names

[David_Grove]

I have two fields in a table for a sheet music library: Last Name and Given Names.

Is there a formula expression that will produce the initials for the given names?

For example, If the Given Names field is just “Johann”, I would like to produce the string “J.”. If the Given Names field is “Johann Sebastion”, I would like to produce the string, "J. S. ". If the Given Names field is “Carl Philipp Emanuel”, I would like to produce the string, "C. P. E. ". Etc.

Thank you for any comments.

DG

[Rebecca_Elam]

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

[ScottWorld]

@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:

Find Formula - Multiple Instances Of String Formulas

Thanks for the additional info. Not knowing more about these emails, it’s probably safe to assume that they’re all different enough that finding a pattern in them isn’t going to be nearly as straightforward as it was with the attachment field URL extraction. Thankfully that doesn’t mean we’re out of luck. When a single formula becomes too much of a headache, I switch to using multiple formulas. The first formula will pad a ton of spaces around each URL, which will allow us to grab each URL as a…

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.

[Bill_French]

ScottWorld:

the best long-term solution is to break apart your name field into separate fields.

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] + ".")

[kuovonne]

@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

Airtable

Grid view - Airtable

Explore the "Grid" view on Airtable.

Here is a form where you can test it out

Airtable

Input for Demo - Airtable

Fill out the "Input for Demo" form quickly and securely on Airtable.

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.

[kuovonne]

ScottWorld:

There is only one known way of separating a single name field …

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.

Bill.French:

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] + “.”)

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.

[Bill_French]

kuovonne:

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

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

[kuovonne]

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.

[Bill_French]

kuovonne:

I think this syntax is problematic, as it doesn’t handle arbitrary lengths well.

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.

[Justin_Barrett]

kuovonne:

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.

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.