|
Posted to microsoft.public.excel.misc
|
|
|
How do I merge two cells without deleting data from the cell?
Thanks Gord...
"Gord Dibben" wrote:
2. Change the de-limiter from " " or "," to Chr(10) and set the cell to
wrap text.
If Len(Cell.Text) 0 Then sbuf = sbuf & Cell.Text & Chr(10)
1. The function cannot copy anything to anywhere.
Functions return results to the cell in which they are written.
You can manually Copy and paste specialvalues to next door cell.
Gord
On Wed, 2 Jun 2010 11:15:12 -0700, GonzaloRC
wrote:
Hi Gord,
Is there a way I can specify so that the function will:
a) change the results from target cell to next door neighboring cell as values
b) Separate out each string as a line within the same cell, as if I had used
the alt+Enter option myself?
Thanks,
"Gord Dibben" wrote:
You can copy the UDF into a general module in your Personal.xls which will
make it available for all open workbooks.
You could copy/paste it into a general module in a new workbook, save that
workbook as an Add-in which you would load through ToolsAdd-ins.
I prefer the Add-in method so's I don't have to preface the Function with
the workbook name.
i.e. if saved in Personal.xls you must enter
=Personal.xls!ConCatRange(range)
If stored in the add-in =ConCatRange(range) is sufficient.
Gord
On Wed, 5 Nov 2008 06:22:00 -0800, rapid1
wrote:
Works perfectly Gord - and please excuse my noobness, but how do I make the
function available to all spreadsheets that I open without have to recreate
the function each time?
Ray D
"Gord Dibben" wrote:
Not without a User Defined Function like this one.
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 & " "
' for comma-delimited change above " " to ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function
Usage is: =concatrange(A1:Z1)
No blank cells will be ignored.
For similar methods with code see this search result from google
http://tinyurl.com/6ao6k4
Gord Dibben MS Excel MVP
On Wed, 6 Aug 2008 06:46:01 -0700, anthony561fl
wrote:
This works great. However, what if Im wanting to combine several columns, say
50 or 100 columns worth of data? Id hate to have to enter each cell name in
that formula. Is there a way to specify a range of columns or cells rather
than each one before and after ampersands?
.
|