Formula Help - If/Then Concatenate?

IF(AND({First Name} != BLANK(), {Last Name} != BLANK()), CONCATENATE({Last Name}, “, “, {First Name},” “,Company), Company)

Try that

Darn. No luck. Invalid Formula error. Thanks for taking a stab at it though!

IF(AND({First Name}!=BLANK(),{Last Name}!=BLANK()),CONCATENATE({Last Name},", ",{First Name}," ",Company),Company)

Sorry - I think somewhere in the copy-paste process the wrong character got put in for the "s. That one should work, and if it doesn’t, manually delete the "s and replace them with your keystroke for that character.

Hi Jeremy,

Tha formula actually doesn’t report an error “invalid formula” like the first one you posted, so technically it works, but it still leaves a space and comma before the company name when there isn’t first and last name.

You are so close!!! I just want the names to line up flush in the column justified to the left. Thanks a bunch for your help!

IF(AND({First Name},{Last Name}),{Last Name} & ", " & {First Name} & " ",BLANK()) & Company

IF(AND({First Name},{Last Name}),{Last Name} & ", " & {First Name} & " ",BLANK()) & Company

That did it! You are the man!! Thanks so much

Hello @Jeremy_Oglesby,

I was wondering if you could help me with a similar issue. I have the following four columns:

Mtg Code
Attendee 1
A1 Mtg ID
A1 Mtg Code

I’d like to populate A1 Mtg Code with a concatenate of Mtg Code and A1 Mtg ID but only if Attendee 1 contains text. I know how to concatenate, and I know how to use IF statements, but I can’t quite figure out how to use them together.

I’ve tried several variations of your formula here with no luck. I’m sure I’m overlooking the obvious. Would you have any suggestions?

Thanks so much!

Amy

What do you want it to contain if {Attendee 1} does not contain text?

This formula will fill {A1 Mtg Code} with “Mtg Code - A1 Mtg ID” if there is anything in {Attendee 1}, otherwise it will leave {A1 Mtg Code} blank:

IF(
   {Attendee 1},
   {Mtg Code} & " - " & {A1 Mtg ID},
)

IF(
   {Attendee 1},
   {Mtg Code} & " - " & {A1 Mtg ID},
)

This is exactly what I needed. Thank you so much!!!

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

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 D:
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.

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

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!

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

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

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

@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}))))))

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

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)

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.

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.

Have you tried just concatenating all the fields with no conditionals? Barring any mistaken entries, a blank field should come through as an empty string, which will not render anything in your Title field. So try this:

{Marketing Lead} &
{Graphics Lead} &
{Media Production Lead} &
{Editorial Lead} &
{Formatting Lead} &
{Production Lead}

Might be that this does what you need.

{Marketing Lead} &
{Graphics Lead} &
{Media Production Lead} &
{Editorial Lead} &
{Formatting Lead} &
{Production Lead}

WOW. What a brilliantly simple answer. Works like a charm!

I know this is an old thread but I have a similar question (I think). I am trying to collate information from a whole lot of tables and rollups into one column as an average of the values. In order to do that, I need the information from 5 columns that are all formulas (columns B,C,D,E,F on screenshot) to show in column 'G' (Total average expenses). Is there a way to do this that doesn't include the NaN values?