Using IF within a CONCATENATE formula

[Sharon_Visser]

I need some help using an IF statement within a CONCATENATE formula. I have tried many permutations but have just not found the right syntax.

I have the following fields:

• AirtableID
• CompanyName
• Project
• Sub-Project
• Person

In the Name field of a table, I want to concatenate these fields as such:

If Sub-Project is not NULL, then Name = AirtableID_CompanyName_Sub-Project_Person
Else (Sub-Project is NULL), then Name = AirtableID_CompanyName_Project_Person

Here is as far as I got, and I know the syntax is not right:
CONCATENATE({AirtableID},’’,{CompanyName},’’,IF({Sub-Project} = 0,{Project},{Sub-Project}),’_’,{Person})

Thanks!

[JonathanBowen]

Hi @Sharon_Visser - I tend to use & rather than CONCATENATE as it is a bit easier to read (IMO), but you’ve totally got the right idea with this:

If Sub-Project is not NULL, then Name = AirtableID_CompanyName_Sub-Project_Person
Else (Sub-Project is NULL), then Name = AirtableID_CompanyName_Project_Person

There’s a few ways to express this, but this one reads the same as your pseudo-code:

IF(
  {Sub-project},
  {AirtableID} & '_' & {CompanyName} & '_' &  {Project} & '_' & {Sub-project} & '_' & {Person},
  {AirtableID} & '_' & {CompanyName} & '_' &  {Project} & '_' & {Person},
)

which reads as "If sub-project exists, then ‘first line’, else ‘second line’ "

JB

[JonathanBowen · Accepted solution]

Slight amendment as project is not included when sub-project exists:

IF(
  {Sub-project},
  {AirtableID} & '_' & {CompanyName} & '_' & {Sub-project} & '_' & {Person},
  {AirtableID} & '_' & {CompanyName} & '_' &  {Project} & '_' & {Person},
)

[Sharon_Visser]

Thanks so much Jonathan - I had to remove that last comma, but otherwise its exactly what I needed! And I agree that & is much easier to read than CONCATENATE. The way you wrote it out in the IF statement is logically how I think about what I wanted it to do. I also figured out a way to embed the IF statement, but your way is much cleaner so I am going with that. Thank again!!!

[JonathanBowen]

Yes, you’re right - an extra comma at the end that’s not needed!

[Graham_Lindsay]

Thanks, this is the solution I was looking for too.

[MaryHartberg]

So, is :&” the same as CONCATENATE?

[Sharon_Visser]

Yes, you can use “&” instead of CONCATENATE:
If you have fields: ‘First Name’ and ‘Last Name’, you can combine them to create “Full Name”

1. {First Name}&" "&{Last Name}
2. CONCATENATE({First Name}," ",{Last Name})

Both will give the same result. I just find using the “&” to be easier.

[mattwolachmix]

was looking for this solution for HOURS and finally figured out how to re-work my formula!