Multiple lines in cell using functions

You must already know that when you press Enter after you modify a cell, the cell content will be saved and you jump down to cell below (we can change that behavior by the way, but that is another post).

And you could have already know that if you want to have multiple lines inside a cell, you need to press ALT+ENTER to do that.

But, how can we do that in formula ??

I got a request on how to do that in a spreadsheet, so, the client wanted to convert two columns having two parts of address, Column C has Street address and street name, and column D has city, state and Zip code.

Then after we do that, we need the new cell to have street address in one line, then the line below to have City, State and Zip code, that was the first request.

Yet, we do not see that ENTER here, what we need to do now is to make sure that the cell has “Wrap text” on, since Excel could not recognize the ENTER (Versus if you do it with ALT+ENTER), once we do that, we can see that ENTER (which is represented by CHAR(10) ) inside the cell

Then after we did that, the client was like, “Ok, how about if we do the Zip code in third line?”

I always got that, I mean the client feels that we did so easily, that means we con push it further….

The main challenge was to find something that we cam tell the formula to replace it with “Enter”, in our situation, we always have the Zip code starting with 1, so the final formula was like this

=CONCATENATE(B4," ",CHAR(10),SUBSTITUTE(C4,", 1",CHAR(10)&"1"))

Let me know if that helped, or if you have other ideas