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:

Name Street1 Street2 Bayside, New York 11360

=== Name1 Street 1 Brooklyn, New York 11235

<colgroup><col></colgroup><tbody>
</tbody>

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