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!

[Jeremy_Oglesby]

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

Try that

[Chris_Livingsto]

Jeremy_Oglesby:

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

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

[Jeremy_Oglesby]

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.

[Chris_Livingsto]

Jeremy_Oglesby:

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

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!

[Jeremy_Oglesby]

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

[Chris_Livingsto]

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

[Amy_Ball]

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

[Jeremy_Oglesby]

Amy_Ball:

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.

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

[Amy_Ball]

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