Combining Concatenate and Regex

[Damian_Evans]

Hi,
I have this formula:

Centre & "-" & {Musician Educators} & "-" & Time

Which is giving me Primary Record IDs like this:

Dublin-Johnny Teacher-12:00-12:30

which is great, but what I really want is:

Du-JT-12:00-12:30

So regex’ing the first two letters of the first field, and the first letters of the two names of the second (I can ensure all teachers are listed as simple first and last names.).

It would make my Primary Fields a lot shorter, clearer, and easier to find when entering in data.

Is that easily done?
Thanks!

[TheTimeSavingCo]

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

[kuovonne]

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.

[Databaser]

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

[kuovonne]

Databaser:

Could you explain the Regex @kuovonne ?

"(.)[^ ]* (.).*",
"$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).

[Damian_Evans]

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!

[Damian_Evans]

Thanks @Adam_TheTimeSavingCo

Is that JS or a special Airtable code?

[TheTimeSavingCo]

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

[Damian_Evans]

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