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!


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

Try that


#3

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


#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.


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


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

#7

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