Find First letters of complicated first names using formulas

Platform discussions
1

I am trying to get the first letters of first names. The problem I have is adding it all together in one formula. Individually they work fine. These are some sample names:

  • Sven
  • Tamara-Siers
  • Pieter Hidde

The results have to be:

  • S.
  • T. S.
  • P. H.

The formula I now have gives an error but I do not know why, could anyone help?

REGEXEXTRACT({Voornaam}, "^[A-Z]") & "."
&
IF(REGEXMATCH({Voornaam}, ".*-.*"), " " & (REGEXEXTRACT({Voornaam}, "-([A-Z])") & "."), "")
&
IF(REGEXMATCH({Voornaam}, "\s"), (REGEXEXTRACT({Voornaam}, "\s[A-Z]") & "."), "")
2

@svenimhoz

The problem comes from

REGEXEXTRACT({Voornaam}, "-([A-Z])")

If you create a formula with only this syntax, you’ll see that if the first name is without hyphen, the column cell is empty. You would think that it returned an empty string…

But if you test it by changing the syntax to the following

ISNONTEXT(REGEXEXTRACT({Voornaam}, "-([A-Z])"))

the result is True. So, in fact, it does NOT return an empty string.

The question is what does it return then?

ISBLANK(REGEXEXTRACT({Voornaam}, "-([A-Z])"))

returns True, i.e. it returns an empty object.

Why the error?

For 2 reasons:

  • when you do " " & (REGEXEXTRACT({Voornaam}, “-([A-Z])”) and the firstname has no hyphen, you’re concatening a space with an empty object which is not possible therefore the error. You’re probably thinking “this is not true because I added an if statement checking if there is an hyphen in the name”… Indeed but you need to know the following:
  • when you run an if statement, If(Condition, resultIfTrue, resultIfFalse), as counterintuitive as it may seem, resultIfTrue and resultIfFalse are “calculated” first then only the one meeting the condition is returned by the function. It seems logical to assume that if the condition is met, resultIfFalse is never calculated because it is unnecessary to calculate it. But, in fact, both are calculated upfront. So if one of the 2 potential results generates an error no matter whether the condition is met or not, you’ll get an error.

Now, what is the SOLUTION?

The solution is to NOT concatenate your space and period within the if statement with the REGEXTRACT function.

Try the following. It should work.

REGEXEXTRACT({Voornaam}, "^[A-Z]") & "." & 
IF(ISBLANK(REGEXEXTRACT({Voornaam}, "-([A-Z])")),"", " ") & 
IF(ISBLANK(REGEXEXTRACT({Voornaam}, "-([A-Z])")),"", REGEXEXTRACT({Voornaam}, "-([A-Z])")) & 
IF(ISBLANK(REGEXEXTRACT({Voornaam}, "-([A-Z])")),"", ".") & 
IF(ISBLANK(REGEXEXTRACT({Voornaam}, "\s[A-Z]")), "", REGEXEXTRACT({Voornaam}, "\s[A-Z]")) & 
IF(ISBLANK(REGEXEXTRACT({Voornaam}, "\s[A-Z]")), "", ".")

2023-01-12_10-39-38

3

Thank you so much! It works now, and I understand it also :grin:

1 Like