Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Text From Cells
If I have 100 cells that contain text and I want to write an Excel
formula that will combine all cell texts into cell without using =a1&a2&a3 what would I do? The volume of cells is too large to use the & in the formula and the transpose feature does not combine text into one cell. A1 = one A2 = two A3 = three A4 = four A5 = five I want B1 to = one two three four five |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Text From Cells
You really want them stacked as per your B1 example?
Set B1 to wraptext and row 1 to autofit. Function ConRange(CellBlock As Range) As String For Each Cell In CellBlock ConRange = ConRange & Cell.Value & Chr(10) Next End Function =ConRange(A1:A100) entered in B1 Chr(10) is a linefeed....................remove the & Chr(10) if you don't need it. Gord On Fri, 13 Jan 2012 08:49:06 -0800 (PST), wrote: If I have 100 cells that contain text and I want to write an Excel formula that will combine all cell texts into cell without using =a1&a2&a3 what would I do? The volume of cells is too large to use the & in the formula and the transpose feature does not combine text into one cell. A1 = one A2 = two A3 = three A4 = four A5 = five I want B1 to = one two three four five |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Text From Cells
How about this one-liner macro...
Sub JoinColumnA() Range("B1").Value = Join(WorksheetFunction.Transpose(Range("A1:A" & _ Cells(Rows.Count, "A").End(xlUp).Row)), vbLf) End Sub Rick Rothstein (MVP - Excel) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Text From Cells
On Jan 14, 3:14*pm, "Rick Rothstein"
wrote: How about this one-liner macro... Sub JoinColumnA() * Range("B1").Value = Join(WorksheetFunction.Transpose(Range("A1:A" & _ * * * * * * * * * * * Cells(Rows.Count, "A").End(xlUp).Row)), vbLf) End Sub Rick Rothstein (MVP - Excel) This works great....thank you !! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining cells with text | Excel Worksheet Functions | |||
Combining text cells. | Excel Discussion (Misc queries) | |||
Combining text from different Cells IF | Excel Discussion (Misc queries) | |||
combining text from 2 cells | Excel Discussion (Misc queries) | |||
Combining Text and numbers from two cells | Excel Discussion (Misc queries) |