Sep 08, 2019 01:32 PM
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:
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!
Solved! Go to Solution.
Sep 09, 2019 12:23 AM
Slight amendment as project is not included when sub-project exists:
IF(
{Sub-project},
{AirtableID} & '_' & {CompanyName} & '_' & {Sub-project} & '_' & {Person},
{AirtableID} & '_' & {CompanyName} & '_' & {Project} & '_' & {Person},
)
Sep 09, 2019 12:21 AM
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
Sep 09, 2019 12:23 AM
Slight amendment as project is not included when sub-project exists:
IF(
{Sub-project},
{AirtableID} & '_' & {CompanyName} & '_' & {Sub-project} & '_' & {Person},
{AirtableID} & '_' & {CompanyName} & '_' & {Project} & '_' & {Person},
)
Sep 09, 2019 06:55 AM
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!!!
Sep 09, 2019 10:37 AM
Yes, you’re right - an extra comma at the end that’s not needed!
May 27, 2022 06:58 PM
Thanks, this is the solution I was looking for too.
May 28, 2022 08:12 PM
So, is :&” the same as CONCATENATE?
May 31, 2022 12:29 PM
Yes, you can use “&” instead of CONCATENATE:
If you have fields: ‘First Name’ and ‘Last Name’, you can combine them to create “Full Name”
Both will give the same result. I just find using the “&” to be easier.
Jan 16, 2023 11:41 AM
was looking for this solution for HOURS and finally figured out how to re-work my formula!