Skip to main content
Solved

Formula - if statement and concatenate

  • September 10, 2022
  • 7 replies
  • 60 views
J

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:

Best answer by Adam_Mintram

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


7 replies

A
Forum|alt.badge.img+2
  • Adam_Mintram
  • New Participant
  • September 10, 2022

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

J
  • Juraj_Ivkovac
  • Author
  • New Participant
  • September 10, 2022

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


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?

A
Forum|alt.badge.img+2
  • Adam_Mintram
  • New Participant
  • Answer
  • September 10, 2022

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
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • September 11, 2022

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?


IF(
    {Suffix},
    CONCATENATE(
        {Prefix},
        " ",
        {First Name},
        " ",
        {Last Name}
    ),
    CONCATENATE(
        {Prefix},
        " ",
        {First Name},
        " ",
        {Last Name},
        ", ",
        {Suffix}
    )
)
J
  • Juraj_Ivkovac
  • Author
  • New Participant
  • September 11, 2022

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 !

A
Forum|alt.badge.img+2
  • Adam_Mintram
  • New Participant
  • September 11, 2022

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

All the best,
Adam

J
Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • September 12, 2022

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

Terms & ConditionsAccessibility statement