How to combine 3 fields with IF statements?

I am sure this is easy, but I can’t figure it out on my own. I’d very much appreciate if some could point me in the right direction.

I have 3 fields: {WRITER 1} {WRITER 2} {WRITER 3}
I am looking for a formula to combine them into one field.
The names should be separated by commas in the new combined field.
I can do the above easily, BUT:
The fields are not always filled. Sometimes only one of them is filled, sometimes all 3.
So I need IF statments in combination with =BLANK()
I can do it for WRITER 1 and 2, but I can’t get the 3rd one in…

Thank you in advance! I am lost.

I can do it with two WRITER fields:
{WRITER 1} & IF({WRITER 2}=BLANK(),"",(", “ & {WRITER 2}))

But how do I get WRITER 3 into this formula? I’m sure it looks easy when I seen it… :wink:

Thanks again in advance!

I may have missed something, but I believe this will work for you:

{Writer 1}
&IF(AND({Writer 1},OR({Writer 2},{Writer 3})),", ","")
&{Writer 2}
&IF(AND({Writer 2},{Writer 3}),", ","")
&{Writer 3}

Also a quick tip, the BLANK() part is not needed as IF({Writer 1}, true, false) will evaluate if writer1 is blank or not without needing the BLANK() part.

Cheers :slight_smile:

Thank you so much, Kris. You are the best.
There were some extra quotation marks and commas that I didn’t need, but when I deleted those, it worked beautifully!

{WRITER 1}
&IF(AND({WRITER 1},OR({WRITER 2},{WRITER 3})),", “)
&{WRITER 2}
&IF(AND({WRITER 2},{WRITER 3}),”, ")
&{WRITER 3}

My pleasure! Glad it worked out.

Was my bad not using the code block in my response before (which gave you the weird quotes).

Kris - one more quick question, since you said I don’t need to use BLANK(), since AND() achieves the same thing. BLANK() is an argument I can follow. I have a hard time translating this into the AND() way.

I have this formula: I am combining 2 fields - {Field 1} and {Field 2}. Unless {Field 2} is empty it adds the field with parentheses around it.

{Field 1}
&IF(
{Field 2}=BLANK(),
"",
" ("
&{Field 1}
& ")"
)

How would that look without using BLANK() ?

Thank you so much. I really do appreciate your help.

@Markus_Wernig, just for claification, the AND() and BLANK() are doing different things. What I was saying is that:

IF({Field}=BLANK(),true,false)

Doesn’t need the blank part. Just do this:

IF({Field},true,false)

It does reverse the order, since the first if statement is seeing if a field is blank, and the other is seeing if the field has data. (So the first IF statement above, true would mean the field is blank, but in the second IF statement true would mean that the field is not blank.)

Hopefully that clarifies.

In terms of adding parenthesis, you can do something like this:

IF({Field 2},"("&{Field 2}&")","")

So if the Field 2 contains data, it will output “(Field 2)”, otherwise it will output nothing.

Thank you so much, @Kris - I really appreciate it. Yes, this is beginning to make sense now.

However, when I try your suggestion:

{Field 1}&IF({Field 2},"("&{Field 2}&“)”,””)

I am getting a formula error message.

No need to chime in right away - I have the BLANK()=0 version in the meantime.
I am just trying to understand this beast…

Thank you!

Hi Markus, just tested it quick and it works just as you have it – without the funny quotes. You could add a space between the words though with this:

{Field 1}&IF({Field 2}," ("&{Field 2}&")","")

Careful copying and pasting into anything like microsoft word as it may change the quotes. A notepad works or just copy directly into the formula field. If it’s giving you an error, then double check the field names are correct since it is working on my end :slight_smile:

Hi Kris,

it worked! I took your advice and switched to a plain text editor. It must have been the quotes getting changed in the process.

Thanks again. I really appreciate your help.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.