Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
Return a cell value based on specific combinations of cells in an array | Excel Worksheet Functions | |||
Truncating a columns worth of cells not just a single cell. | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Can vlookup return multiple matches in a single cell? | Excel Discussion (Misc queries) |