Formula - if statement and concatenate

[Juraj_Ivkovac]

Hey clever people,

I got following single line txt fields in my table:
{Prefix}, {First Name}, {Last Name}, {Suffix}.

I already use a formula to concenate this data:
CONCATENATE({Prefix}," “,{First Name},” “,{Last Name},” ",{Suffix})
… this works fine.

My problem/question, is it possible to make a formula that will put between {Last Name} and {Suffix} a comma if {Suffix} is not empty/null, instead of the space like above. If I replace the space with a comma it will obviously be everywhere, what I don´t want.

For example:

1. Prefix (Dr.), First Name (John), Last Name (Miller), Suffix (M.Sc.) - Formula Result: Dr. John Miller, MSc.

2. Prefix (Dr.), First Name (Leroy), Last Name (Thompson), Suffix (null) - Formula Result: Dr. Leroy Thompson

I hope you understand what I mean :grinning_face_with_big_eyes:

[Adam_Mintram]

Hey @Juraj_Ivkovac,

If I have understood correctly, you’re correct in that you want to use an IF statement as below.

IF(Suffix != “”, CONCATENATE({Prefix}," “,{First Name},” “, {Last Name},”, “, {Suffix}), CONCATENATE({Prefix},” “,{First Name},” ", {Last Name}))

What the above formula is doing is:
IF the Suffix field is not equal to “null”, Concatenate these fields
{Prefix}," “,{First Name},” “, {Last Name},”, ", {Suffix})

Otherwise, IF it is null, only concatenate these fields
{Prefix}," “,{First Name},” ", {Last Name}

I hope that helps! :slightly_smiling_face:

Thanks,
Adam

[Juraj_Ivkovac]

Hey @Adam_Mintram :slightly_smiling_face:
Thank you very much for your guidance.

For some reason it´s not working for me … is there maybe a small syntax problem?

[Adam_Mintram · Accepted solution]

Hey,

Oh that’s strange! I wonder if the first Suffix needs {} around it.
It is working for me so I wonder if my field names are slightly different to yours.

Otherwise, if you delete and then try to manually input the formula (using the existing as a reference) to ensure it links all the fields ok.

Let me know how you get on? I will see if I can find anything else in the meantime.

Adam

[Ben_Young1]

IF(
    {Suffix},
    CONCATENATE(
        {Prefix},
        " ",
        {First Name},
        " ",
        {Last Name}
    ),
    CONCATENATE(
        {Prefix},
        " ",
        {First Name},
        " ",
        {Last Name},
        ", ",
        {Suffix}
    )
)

[Juraj_Ivkovac]

Hey @Adam_Mintram,

Thanks for your screenshot. Now I have found the error. By copying your code probably a wrong formatting for the quotes was transferred and in further consequence the code was corrupted. If you look at the quotes in my code, you can see that they have different formatting.

Problem solved - Thank you very much, I am very happy with the result. :grinning_face_with_big_eyes: Thanks also to @Ben.Young !

[Adam_Mintram]

Ah brilliant @Juraj_Ivkovac !
Glad you managed to sort it :muscle: .

All the best,
Adam

[Alexey_Gusev]

Hi,
Despite the case is solved, I would suggest to use a bit different approach in similar cases. In some complex formulas that helped me to avoid double use of ‘complex’ part.

CONCATENATE({Prefix},' ',{First Name},' ',{Last Name},IF({Suffix},', '&{Suffix}))