Formula Help - If/Then Concatenate?

[Chris_Livingsto]

I have one field that I would like to generate from three other ones. First Name, Last Name and Company

If there is a first and last name in the directory I would like to display it as the following:

Last Name, First Name

If there is no person’s name then I would like it to only display the name of the company. I just don’t know how to remove the space and comma. Currently using this:

CONCATENATE({Last Name}, ", “, {First Name},” ",Company)

I’m thinking I need some sort of IF/THEN statement??

Thanks!

[Amy_Ball]

Hi Jeremy,

Thanks again for your help yesterday.

If I might trouble you further, is there a formula that can populate one field by searching the text contained in another field for a specific value within that text?

I have a field that contains a list of equipment that is separated by commas. I’d like to create separate fields for each type of equipment and then populate those columns automatically via a formula if specific text appears somewhere in the equipment field. For example:

Fields:
All Equipment (Text field)
Projector (Formula)
Screen (Formula)
Lectern (Formula)

Example Record A:
All Equipment: Projector, Screen
Projector: 1
Screen: 1
Lectern: Blank

Example Record B:
All Equipment: Projector, Screen, Lectern
Projector: 1
Screen: 1
Lectern: 1

Example Record C:
All Equipment: Blank
Projector: Blank
Screen: Blank
Lectern: Blank

Hopefully that makes sense.

Thank you!

Amy

[Jeremy_Oglesby]

Yes, this should be possible. In your examples, you only ever have 1 instance of a type of equipment – will that always be the case? Will something like this ever be needed:

Example Record 😧
All Equipment: Projector, Projector, Screen, Screen
Projector: 2
Screen: 2
Lectern: Blank

I’m not certain that the above is possible, at least not in an easy/straight-forward fashion…

But if all you need is to recognize the presence of any number of “Projectors” or any number of “Screens”, etc, in the “All Equipment” field, then this simple formula should do it:

In the “Projector” formula field:

IF(
   FIND(
      "Projector",
      {All Equipment}&""
   ),
   1
)

But again, that will return “1” even if there are 4 instances of the word “Projector” in the “All Equipment” field.

[Amy_Ball]

Thanks, Jeremy! This, again, is exactly what I needed as I’ll only ever have one of each type of equipment.

Have a great day!

[John_Dlouhy]

Hello Jeremy!

I am hoping you can help with a formula. I am building a base to track event attendees. The table includes a “First” field to list the first names. Every record in this table will have data in the “First” field. Another field is called “With First” and will only have data when someone brings a guest. I need a “Salutation” formula field that would yield the following results:

If “With First” is blank, enter the information from the “First” field.
Example: James is coming alone, therefore the “Salutation” field would simply be James.

If “With First” is NOT blank, enter the information from the “First” field, add " and ", add information from “With First” field. Example: James is bringing Sally, therefore the “Salutation” field would be “James and Sally.”

I’m relatively new to Airtable and am learning quickly–but anything beyond basic formulas is posing a challenge!

With best regards,

John

[Jeremy_Oglesby]

{First} &
IF(
   {With First},
   " and " & {With First}
)

[John_Dlouhy]

Jeremy_Oglesby:

{First} & IF( {With First}, " and " & {With First} )

G E N I U S !!! Thanks so much Jeremy!! Incredible help.

[Lisa_Nelson]

@Jeremy_Oglesby Thank you! Your answers to these other questions helped me write my formula.

I wanted to return results that had Job # - Company, unless there was no Company. In that case, I wanted Job # - Contact Last. In the event that there was no Job #, I wanted to have Proposal # - Company, unless there was no Company, then I wanted Proposal # - Contact Last. In the event there is no Job # or Proposal #, I wanted Company or Contact Last if there was no Company.

My head was spinning! Your examples helped we write the following formula that does just what I needed!

IF(AND(Company,{Job #}),{Job #}&" - "&Company,

IF(AND({Contact Last},{Job #}),{Job #}&" - "&{Contact Last},

IF(AND(Company,{Proposal No}),{Proposal No}&" - "&Company,

IF(AND({Contact Last},{Proposal No}),{Proposal No}&" - "&{Contact Last},

IF(Company,Company,

IF({Contact Last},{Contact Last}))))))

[Nicole_Flewelle]

@Jeremy_Oglesby Another If/Concatenate conundrum for you!

I have fields for each of 6 teams (Marketing, Graphics, Media Production, Editorial, Formatting, and Production) that I am trying to concatenate with a single other field (Document/Project Title). However, for each record, only one of those team fields will be filled in, so I need a formula that says:

IF Marketing, then Marketing and Document/Project Title; IF Graphics, then Graphics and Document/Project Title and so on.

Thanks in advance!

[Jeremy_Oglesby]

Nicole_Flewellen:

I have fields for each of 6 teams

What kind of fields are these? Are they Linked Record fields? Single Select fields? May I ask why you have 6 different fields, instead of one field that holds all the Team options? (I’m sure you have a reason, I’m just curious what it is)

Nicole_Flewellen:

for each record, only one of those team fields will be filled in

Does this mean it will alway, only, ever be the case that one of these fields will be filled in, while the other 5 are blank?

I want to withhold from offering an answer until you are able to clarify a bit, because the solution may depend on your configuration.

[Nicole_Flewelle]

The fields are Collaborator fields. They’re for assignments (so I should correct my field titles, they’re actually Marketing Lead, Graphics Lead, Media Production Lead, Editorial Lead, Formatting Lead, and Production Lead)

There will always, only, ever, be one of these fields filled in at a time. We’re using a form to populate an OOO calendar (which integrates with our workload calendar, which is why there are separate fields for each team and not just a single “Name” field).

So, on the form, folks select their team (Marketing, Graphics, etc.), which then, through conditional formatting, displays the corresponding “Lead” Collaborator field, from which they select their name. So when the form entry comes through, only one “Lead” field will be filled in.