Formula Help - If/Then Concatenate?

#1

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!

0 Likes

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

Try that

0 Likes

#3

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

0 Likes

#4

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.

0 Likes

#5

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!

0 Likes

#6
IF(AND({First Name},{Last Name}),{Last Name} & ", " & {First Name} & " ",BLANK()) & Company
2 Likes

#7

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

0 Likes

#8

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

0 Likes

#9

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},
)
1 Like

#10

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

1 Like

#11

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

0 Likes

#12

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.

1 Like

#13

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!

1 Like