Help

Re: Formula - if statement and concatenate

Solved
Jump to Solution
1083 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Juraj_Ivkovac
4 - Data Explorer
4 - Data Explorer

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:

1 Solution

Accepted Solutions
Adam_Mintram
5 - Automation Enthusiast
5 - Automation Enthusiast

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

image
Screenshot 2022-09-10 at 21.17.35

See Solution in Thread

7 Replies 7
Adam_Mintram
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

Bildschirmfoto 2022-09-10 um 8.59.17 PM

Adam_Mintram
5 - Automation Enthusiast
5 - Automation Enthusiast

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

image
Screenshot 2022-09-10 at 21.17.35

IF(
    {Suffix},
    CONCATENATE(
        {Prefix},
        " ",
        {First Name},
        " ",
        {Last Name}
    ),
    CONCATENATE(
        {Prefix},
        " ",
        {First Name},
        " ",
        {Last Name},
        ", ",
        {Suffix}
    )
)
Juraj_Ivkovac
4 - Data Explorer
4 - Data Explorer

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

All the best,
Adam

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