Alt + Enter Character Code in Concatenate formula

[moe10134]

I'm trying to combine individual cells into one, with a Concatenate function using a character code. The character code for a Alt+ Enter is CHAR10, I believe. When creating a formula using concatenate and entering =CHAR(10), CHAR10, or CHR10 it does not put a hard return in the cell. Plus it puts " around all the "CHAR(10)".

I'd like to turn: John Smith 1234 Main St. Anywhere, CA 10134 in cell A1 to:

John Smith
1234 Main St.
Anywhere, CA 10134

into cell B1. Can this be done? Which CHAR code do I use in a Concatenate?

Any Suggestions? Much Appreciated,
MOE10134

 

[VoG]

Maybe like this - B1 must be formatted to wrap text. Note I added a comma at the end of the name in A1

Excel Workbook
	A	B
1	John Smith, 1234 Main St. Anywhere, CA 10134	John Smith 1234 Main St. Anywhere CA 10134
Sheet2

 

[moe10134]

That was a good suggestion, but what is happening now is when I click on the cell it looks like:

John Smith
1234 Main St.
Anywhere, CA 10134

in the address bar, but does not display that way on the screen in that cell. Weird! Anymore suggestions? Thanks for your help. =)

 

[VoG]

As I said, B1 must be formatted to wrap text. Click in B1, press CTRL + 1, on the Alignment tab check Wrap Text then click OK.

 

[moe10134]

I see where you are going, but I don't want a wrap text. I need a Hard Enter. ALT + ENTER to move it to the next line in the same cell, not just wrap the text. Cell B1:

John Smith (ALT+ENTER)
1234 Main St. (ALT+ENTER)
Anywhere, CA 10134


Concatenate Possibility:
John Smith
(ALT+ENTER) or CHAR10
1234Main St.
(ALT+ENTER)
Anywhere, CA 10134

 

[VoG]

="John Smith"&CHAR(10)&"1234 Main St."&CHAR(10)&"Anywhere, CA 10134"

BUT it will not display on multiple lines unless you set the cell to Wrap Text.

 

[moe10134]

That still does not seem to work. Is it because there is no concatenate in your formula? Or that I am using cell references instead of text? Cell formats are text so I don't see why that would not work The results I get back contains the &CHAR10& in the results. I believe it is because of the " , doesn't that mean insert text instead of a format? They are automatically being entered as I move to the next line in concatenate. But the final results have removed the () and " for final result listed below.

CONCATENATE(A1,"&CHAR10&",A2,"&CHAR10&",A3)

Results: ="John Smith&CHAR10&1234 Main St.&CHAR10&Anywhere, CA 10134"

 

[VoG]

Try

=A1&CHAR(10)&A2&CHAR(10)&A3

 

[moe10134]

PERFECT!!! This I can work with!! Thank you so much for taking the time and patience to work with me on this. I have 2400+ individual entries and didn't want to have to ALT + ENTER every single one. Much appreciated!! Thank you kindly, MOE10134