Abbriviate Second Word

Hi there,

I am trying to shorten names in a table, but taking the second word, and trimming it to 1 character and adding a “.”

Here is the doc

Any ideas about how to do it with a formula?

1 Like

Hi @Robin_Bigio :blush:

I’ve added 2 column to your sample with these formula :blush:

Number 1: Format()

  "{1} {2}.",
  thisRow.[Full Name].Split(" ").Nth(1),
  thisRow.[Full Name].Split(" ").Nth(2).Left(1)

Number 2: Concatenate()

  thisRow.[Full Name].Split(" ").Nth(1),
  " ",
  thisRow.[Full Name].Split(" ").Nth(2).Left(1),

There might me other ways to do this too :innocent: … But I don’t have the time to explore this further right now …


Amazing! Can I ask why you need the thisRow portion? For edification purposes :slight_smile:

Third option is to do RegexReplace():

thisRow.[Full Name].RegexReplace("(?<= .).+", ".")

i.e. replace all characters that come after a space and any character with a dot.


Thank you @Paul_Danyliuk :grin: ! This is exactly where I didn’t have the time to go :raised_hands: !

thisRow.[Full Name] is the value in your column [Full Name] for thisRow :blush: . It’s the text you’ve entered in [Full Name] .

This text value is then Split() by Space (" ") which returns a list of text values.

E.g.: for your very first row thisRow.[Full Name] is Cristi Ramos Cevallos

thisRow.[Full Name].Split(" ")

… returns


From this list, we keep the very first value which is done with this part of the formula

thisRow.[Full Name].Split(" ").Nth(1)

which in this case returns


… And then to abbreviate the 2nd word from this list of text values we Split() the text value in [Full Name] by Space (" ") but keep only the 2nd word (Nth(2)) and from the left, we keep 1 character (Left(1)).
In its entirety this part of the formula is …

thisRow.[Full Name].Split(" ").Nth(2).Left(1)

And in this case it returns


All that’s missing is the period (.) which is added directly either in the Template part of Format() or as just another text in Concatenate() :blush: .

And the final result is:

Cristi R.

For the RegexReplace() @Paul_Danyliuk suggested, well, it does exactly what Paul said :innocent:

… using the regular expression “(?<= .).+” which in RegEx terms is a Positive Lookbehind :blush:, in the text value in [Full Name] it will match whatever comes after a space + 1 character (any character).

For your very first row where thisRow.[Full Name] is Cristi Ramos Cevallos the match is :

amos Cevallos

And this match is then replace by a period (.) :blush: .

So the final result is also

Cristi R.

Thinking about all this, I’ve just added another option using WithName() which allows you to create a “Named value” you can then use like any other value in a formula :blush: .

In my Format() suggestion and my Concatenate() one, you can see that thisRow.[Full Name].Split(" ") is repeated twice…

Using WithName() I can calculate this value one time and then use it later in my Format() formula (for example)

thisRow.[Full Name].Concatenate().Split(" ").WithName(SplitName,
    "{1} {2}.",

First class explanation @Pch, :diamond_shape_with_a_dot_inside:


Thank you @Jean_Pierre_Traets :blush: !

I just hope this will help @Robin_Bigio :blush: !

1 Like

Thanks so so much! This is amazing :slight_smile:

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.