# Re: Formula - if statement and concatenate

Solved
1659 0
cancel
Showing results for
Did you mean:
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
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.

7 Replies 7
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,

4 - Data Explorer

Thank you very much for your guidance.

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

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.

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

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 !

5 - Automation Enthusiast

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

All the best,
`CONCATENATE({Prefix},' ',{First Name},' ',{Last Name},IF({Suffix},', '&{Suffix}))`