Concatenate and If together for Carriage Return

[ericdbernstein]

I am trying to create a formula for a table that contains two street address lines, City, State, Zip.

If there is one street address line, I wanted to suppress the Carriage return. If there are two address lines, I wanted to incorporate both lines.

I can conceive using the Concatenate and If together but am stuck.

ie:
[TABLE="width: 308"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name
Street1
Street2
Bayside, New York 11360[/TD]
[/TR]
[TR]
[TD]===
Name1
Street 1

Brooklyn, New York 11235[/TD]
[/TR]
</tbody>[/TABLE]


Here is the formula:=CONCATENATE(O12,CHAR(10),R12,CHAR(10),S12,CHAR(10),T12,", ",U12," ",V12)

Thank you in advance.

Eric Bernstein

 

[Fluff]

How about
=SUBSTITUTE(CONCATENATE(O12,CHAR(10),R12,CHAR(10),S12,CHAR(10),T12,", ",U12," ",V12),CHAR(10)&CHAR(10),CHAR(10))