Concatenate shortcut?
I have a column in a worksheet containing 110 rows, each of which has a single email address. I would like to concatenate the contents of all 110 cells, each one separated from the next by a comma to be used as a single email address. Is there an easier way to do this other than to write a formula in which I have to identify each and every cell to be included in the concatenation? -- nebb ------------------------------------------------------------------------ nebb's Profile: http://www.excelforum.com/member.php...fo&userid=8981 View this thread: http://www.excelforum.com/showthread...hreadid=492425 |
Concatenate shortcut?
I don't know if you'll be able to contain 110 email addresses in a single
string, but in general I deal with similar situations with a helper column. Suppose your addresses are in B2:B111. In C2, enter =b2. In C3, enter =c2 & ", " & b3. Copy the formula from C3 to C4:C111. C111 will have your complete list. "nebb" wrote: I have a column in a worksheet containing 110 rows, each of which has a single email address. I would like to concatenate the contents of all 110 cells, each one separated from the next by a comma to be used as a single email address. Is there an easier way to do this other than to write a formula in which I have to identify each and every cell to be included in the concatenation? -- nebb ------------------------------------------------------------------------ nebb's Profile: http://www.excelforum.com/member.php...fo&userid=8981 View this thread: http://www.excelforum.com/showthread...hreadid=492425 |
Concatenate shortcut?
nebb
Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox _ ("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Alternative.............UDF 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:A110) Gord Dibben Excel MVP On Sat, 10 Dec 2005 09:55:07 -0600, nebb wrote: I have a column in a worksheet containing 110 rows, each of which has a single email address. I would like to concatenate the contents of all 110 cells, each one separated from the next by a comma to be used as a single email address. Is there an easier way to do this other than to write a formula in which I have to identify each and every cell to be included in the concatenation? |
Concatenate shortcut?
"Gord Dibben" <gorddibbATshawDOTca@ wrote...
.... Alternative.............UDF 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:A110) .... Generality and flexibility are good. Also a little trickery goes a long way. http://groups.google.com/group/micro...e=source&hl=en (or http://makeashorterlink.com/?S1E33459B ) includes a function named mcat that takes a variable number of arguments, like SUM. It could be used in =SUBSTITUTE(TRIM(MCAT(" "&YourRangeAddressHere))," ",",") As for trickery, much better in VBA to append the field separator between sbuf and the next entry, so sbuf = sbuf & "," & Cell.text which puts an extraneous comma at the beginning of sbuf. It's easier and more efficient then to use ConCatRange = Mid$(sbuf, 2) |
All times are GMT +1. The time now is 07:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com