Concatenating & Formatting

[Christi]

I am concatenating several cells.

I would like for each cell I am concatenating to start a new line within my
final cell.

If I am just typing in a cell I can do ALT and Enter to start a new line.
How can I put that in my formula to make the concatenate do that?

 

[Gary''s Student]

=A1 & CHAR(10) & B1
and Format > Cells... > Alignment > Wrap

 

[Christi]

When I type what you said, I get this #NAME?

Here is my original formula:
"CONCATENATE($N$5,"" "",N7)&CONCATENATE($O$5,"" "",O7)&CONCATENATE($P$5,""
"",P7)&CONCATENATE($Q$5,"" "",Q7)&CONCATENATE($R$5,""
"",R7)&CONCATENATE($S$5,"" "",S7)&CONCATENATE($T$5,""
"",T7)&CONCATENATE($U$5,"" "",U7)&CONCATENATE($V$5,""
"",V7)&CONCATENATE($W$5,"" "",W7)&CONCATENATE($X$5,""
"",X7)&CONCATENATE($Y$5,"" "",Y7)&CONCATENATE($Z$5,""
"",Z7)&CONCATENATE($AA$5,"" "",AA7)&CONCATENATE($AB$5,""
"",AB7)&CONCATENATE($AC$5,"" "",AC7)&CONCATENATE($AD$5,""
"",AD7)&CONCATENATE($AE$5,"" "",AE7)
&CONCATENATE($AF$5,"" "",AF7)&CONCATENATE($AG$5,""
"",AG7)&CONCATENATE($AH$5,"" "",AH7)"

This is my result:

Janitorial 0Groundskeeping -90Building Engineer 0Elevator 0HVAC 0Pest
Control 0Plumbing 0Trash 0GB R&M 0Pavement 0Security 0Electricity 0Gas 0Water
0Fuel/Steam 0Occ-Other 0F&E Main. Contract 0F&E Main. 0Small Furniture 0F&E
Rental 0Misc. 0

I want my result to be:
Janitorial 0
Groundskeep -90
Building Engineer 0

etc.

 

[David Biddulph]

If you've got #NAME? my suspicion is that you tried to retype the formula
and mistyped it. Just copy =A1 & CHAR(10) & B1 from the newsgroup and paste
into the formula bar on your spreadsheet. It is isn't that, then perhaps
your Excel is expecting a language other than English?

As far as you own formula is concerned, there are a number of questions.
Firstly why the multiple double quotes? If you want to include a space,
then your concatenation formula would include ...," ",... not ...,"" "",...
Secondly why the strange mixture of CONCATENATE and the & operator?
=CONCATENATE(A1,B1,C1) is the same as =A1&B1&C1.

If you want to include a new line instead of a space, include CHAR(10)
instead of the space " " in your formula.

 

[ShaneDevenshire]

Hi,

Well I've seen this one before, its a pretty ugly formula and adding
&CHAR(10)& between various parts of it makes it all the more ugly. First if
you are trying to put a space between every two entries "" "" will not work,
use " ". A pair of double quotes, not two pairs.

 

[ShaneDevenshire]

Hi again,

I see I have more to add: you might consider replacing this type of thing:
CONCATENATE($N$5,"" "",N7)&CONCATENATE($O$5,"" "",O7)
with
CONCATENATE($N$5," ",N7,$O$5," ",O7)
or
=$N$5&" "&N7&$O$5&" "&O7

both of these are shorter and they don't change the result.

 

[ShaneDevenshire]

Hi once more,

If you are happy with the values being concatenate not the references than
you could use a custom VBA function such as this:

Function MyCon(R As Range) As String
Dim T As String
Dim cell As Range
For Each cell In R
T = T & cell.Offset(-2, 0) & " " & cell
Next cell
MyCon = T
End Function

In a cell you would enter =MyCon(N7:AH7)

If any of these suggestions have helped please click the Yes button.