Help

Combining Concatenate and Regex

Topic Labels: Formulas
133 8
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

8 Replies 8

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).

Labels