Concatenate and If together for Carriage Return

ericdbernstein

New Member
Joined
Dec 10, 2018
Messages
1
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,675
Office Version
365
Platform
Windows
How about
=SUBSTITUTE(CONCATENATE(O12,CHAR(10),R12,CHAR(10),S12,CHAR(10),T12,", ",U12," ",V12),CHAR(10)&CHAR(10),CHAR(10))
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,535
Members
406,485
Latest member
kaksolver

This Week's Hot Topics

Top