Combining Concatenate and Regex

Hm, if you’re alright with not using regex, you could do:

LEFT(
  Centre,
  2
) & 
"-" & 
LEFT(
  {Musician Educators},
  1
) &
LEFT(
  SUBSTITUTE(
    {Musician Educators},
    LEFT(
      {Musician Educators},
      FIND(
        " ",
        {Musician Educators}
      )
    ),
    ""
  ),
  1
) & 
"-" & 
Time

There are many possible formulas. Here is another one.

CONCATENATE(
  LEFT({Centre}, 2),
  "-",
  REGEX_REPLACE(
    {Musician Educators},
    "(.)[^ ]* (.).*",
    "$1$2"
  ),
  "-",
  {Time}
)

However, I think the most robust solution would be a different approach. In your [Centre] and [Musician Educators] tables, create a new {Short Code} text field that you manually enter. Then add lookup fields for these short codes and build your primary field using the lookups of the short codes.

This method will add a few more fields to your base but adds a lot of flexibility. For example, you can better address adding a new teacher with the same initials as an existing teacher, or handle locations that start with the same letters.

Could you explain the Regex @kuovonne ? Very interested in learning more! :pray:

"(.)[^ ]* (.).*",
"$1$2"

I use regex grouping in the REGEX_REPLACE. The parenthesis in the first expression indicate the groups I want to use.

• The set of parenthesis indicates the first group (.), which is a single character as indicated by a dot.
• The next section indicates any characters that is not a space [^ ] repeated zero or more times *, followed by a space: .
• The second set of parenthesis indicates the second group (.), which is also a single character as indicated by a dot.
• The rest of the expression .* is any additional characters that make up the text string.

The replacement expression says to replace the entire expression with the first capture group (as indicated by $1) and the second capture group (as indicated by $2).

Many thanks @Adam_TheTimeSavingCo and @kuovonne.

I can see your point @kuovonne, I’ve already found teachers with the same initials! May I ask how I combine multiple lookups to create a primary field? Presumably this can’t be done with a customise field type but has to be done with a formula?

EDIT: Aah. I added the lookups to the table and then simply combined them. I thought it was more complicated than it was. Thanks!

LEFT(
  Centre,
  2
) & 
"-" & 
LEFT(
  {Musician Educators},
  1
) &
LEFT(
  SUBSTITUTE(
    {Musician Educators},
    LEFT(
      {Musician Educators},
      FIND(
        " ",
        {Musician Educators}
      )
    ),
    ""
  ),
  1
) & 
"-" & 
Time

Thanks @Adam_TheTimeSavingCo

Is that JS or a special Airtable code?

Just an Airtable formula, should work after you put it into a formula field

Ah cheers. It worked a charm. Many thanks. I’ll have to go have a read about Airtable formulas some more. Thanks again!