Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcut Keys -- quick find | Excel Discussion (Misc queries) | |||
Concatenate | Excel Discussion (Misc queries) | |||
how can you customize a shortcut menu in excel? | Excel Discussion (Misc queries) | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) | |||
How to make [toggling] shortcut keys to superscript and subscript, respectively? | Excel Discussion (Misc queries) |