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

E

Enterprise Todd

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?
 
G

Gord Dibben

=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
 
E

Enterprise Todd

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.
 
R

Roger Govier

Hi

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

Format>Cells>Alignment>Wrap Text
 
Top