How do I return the vale of multiple cells into a single cell
 

I am trying to figure out how I can return the values of multiple cells into
one (Merged group) cell for a cleaner presentation, any suggestions?

How do I return the vale of multiple cells into a single cell
 

=A1&B1&C1

OR

=A1 & " " & B1 & " " & C1

Or a UDF which ignores blanks in a range

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:F1)


Gord Dibben MS Excel MVP

On Tue, 10 Oct 2006 13:30:01 -0700, Enterprise Todd <Enterprise
wrote:

I am trying to figure out how I can return the values of multiple cells into
one (Merged group) cell for a cleaner presentation, any suggestions?

How do I return the vale of multiple cells into a single cell
 

Thanks Gordon, that worked well, but is there any way to format the output?
enter a hard return or indentation?

My data cells look like:

Product
Sub Prod
Sub Prod 2
Sub Pro 3

Each cell is the result of a formula. The cell that I'm putting the formula
has A1 - B4 merged so that it would be large enough to display the results,
but if I just use the =A1 & "" & B2 & "" & B3 it returns all of them in a
row.

Ultimately I need/want to combine all of the results into a single cell so
that I can use those results along with about 6 others in a new formula.

Sorry I gues I should have been more explicit in my first question.

"Gord Dibben" wrote:

=A1&B1&C1

OR

=A1 & " " & B1 & " " & C1

Or a UDF which ignores blanks in a range

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:F1)


Gord Dibben MS Excel MVP

On Tue, 10 Oct 2006 13:30:01 -0700, Enterprise Todd <Enterprise
wrote:

I am trying to figure out how I can return the values of multiple cells into
one (Merged group) cell for a cleaner presentation, any suggestions?

How do I return the vale of multiple cells into a single cell
 

Hi

Char(10) puts a line feed in the text
=A1 & Char(10) &" " & B2 & Char(10) &" " & B3

FormatCellsAlignmentWrap Text
--
Regards

Roger Govier


"Enterprise Todd" wrote in
message ...
Thanks Gordon, that worked well, but is there any way to format the
output?
enter a hard return or indentation?

My data cells look like:

Product
Sub Prod
Sub Prod 2
Sub Pro 3

Each cell is the result of a formula. The cell that I'm putting the
formula
has A1 - B4 merged so that it would be large enough to display the
results,
but if I just use the =A1 & "" & B2 & "" & B3 it returns all of them
in a
row.

Ultimately I need/want to combine all of the results into a single
cell so
that I can use those results along with about 6 others in a new
formula.

Sorry I gues I should have been more explicit in my first question.

"Gord Dibben" wrote:

=A1&B1&C1

OR

=A1 & " " & B1 & " " & C1

Or a UDF which ignores blanks in a range

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:F1)


Gord Dibben MS Excel MVP

On Tue, 10 Oct 2006 13:30:01 -0700, Enterprise Todd <Enterprise
wrote:

I am trying to figure out how I can return the values of multiple
cells into
one (Merged group) cell for a cleaner presentation, any suggestions?