Skip to main content

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

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

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


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

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

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


So, is :&” the same as CONCATENATE?

So, is :&” the same as CONCATENATE?


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.

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


IF(

  {Sub-project},

  {AirtableID} & '_' & {CompanyName} & '_' & {Sub-project} & '_' & {Person},

  {AirtableID} & '_' & {CompanyName} & '_' &  {Project} & '_' & {Person},

)

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

Reply


Terms & Conditions