Concatenate and In Cell Line Break

gripper

Board Regular
Joined
Oct 29, 2002
Messages
143
Hi Guys,

I am running Excel 2007. I have a workbook and on the first sheet named "Input" i have a grid with basic address info like this
A5 = John Smith
B5 = 1234 Main Street
C5 = Los Angeles
D5 = CA
E5 = 12345 (Zip)

I have a sheet of these addresses.

On the next tab I have a workbook that looks like mailing labels. This sheet is called "Labels"

What I am trying to do is take the value of row 5 on the Input sheet and place that in the first cell on the Label Sheet (a1) in a specific format like this

John Smith
1234 Main Street
Los Angeles, CA 12345

I have been playing with this formula and I get the first line and the other two cells right after in one long string but without the line breaks I was hoping for. I was hoping you guys could offer me some troubleshooting skills.

=IF(Input!A5="","",Input!A5&CHAR(10)&B5&CHAR(10)&J5)

The J5 reference is where I concatenated the City, State and Zip to make it easier on the Label page.

If there is no data on the Input sheet then I want this cell blank.

Thank you
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

gripper

Board Regular
Joined
Oct 29, 2002
Messages
143
Please disregard. My code was correct but I forgot to allow word wrap in the cell.

Thank you
 

mcmahobt

Board Regular
Joined
Sep 2, 2014
Messages
55
Code:
=A5&CHAR(10)&B5&CHAR(10)&C5&CHAR(10)&D5

Format cell with formula with "Wrap Text"
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,260
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top