I'm trying to concatenate address components (Address Line 1, Address Line 2,
City, State, and Zip Code) from their individual cells into a single cell
that is formatted correctly in 2 or 3 lines of text. In my formula, I'm
trying to find a way to output Address Line 1, start a new line, put Address
Line 2 on the new line, start a new line, and put the City, State, and Zip on
the last line.

You cannot use a function to create a new line. Worksheet functions can
only return values in the cells they are referred to, they cannot
change any thing other than that. You'd have to use VBA to achieve
that.


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=562924

Jon

=A1 & CHAR(10) & B1 & CHAR(10) & C1 will give you 3 lines of text.

Format to "wrap text"


Gord Dibben MS Excel MVP


On Wed, 19 Jul 2006 08:56:03 -0700, Jon Shelton <Jon
wrote:

I'm trying to concatenate address components (Address Line 1, Address Line 2,
City, State, and Zip Code) from their individual cells into a single cell
that is formatted correctly in 2 or 3 lines of text. In my formula, I'm
trying to find a way to output Address Line 1, start a new line, put Address
Line 2 on the new line, start a new line, and put the City, State, and Zip on
the last line.