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]

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.

[Nicole_Flewelle]

Jeremy_Oglesby:

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

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

[Dominique_Sandw]

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?