Using IF within a CONCATENATE formula

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!

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

1 Like

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

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

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!!!

1 Like

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