ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   concatenate a range of data (https://www.excelbanter.com/excel-programming/445508-concatenate-range-data.html)

jt

concatenate a range of data
 
is there a way to concatenate a range instead of having to
individually select each cell, thanks in advance

James Ravenswood

concatenate a range of data
 
Hi-light the range and run Builder, then select an empty cell and run Putter. Here are the macros:

Dim sf As String

Sub builder()
sf = ""
For Each r In Selection
If sf = "" Then
sf = "=" & r.Address
Else
sf = sf & "&" & r.Address
End If
Next
sf = Replace(sf, "$", "")
MsgBox sf
End Sub

Sub putter()
ActiveCell.Formula = sf
End Sub

Gord Dibben[_2_]

concatenate a range of data
 
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) - 2)
End Function

usage is: =ConCatRange(A1:A10)

This gives a comma/space delimited list in one cell.

You can adjust that to suit by changing or removing & ", "


Gord


On Sat, 17 Mar 2012 06:04:15 -0700 (PDT), jt
wrote:

is there a way to concatenate a range instead of having to
individually select each cell, thanks in advance


Jim Cone[_2_]

concatenate a range of data
 
With xl2000 or later versions, you can use the VBA.Join function.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Data Options add-in: rows/dates/random stuff)





"jt"
wrote in message
...
is there a way to concatenate a range instead of having to
individually select each cell, thanks in advance




Ron Rosenfeld[_2_]

concatenate a range of data
 
On Sat, 17 Mar 2012 06:04:15 -0700 (PDT), jt wrote:

is there a way to concatenate a range instead of having to
individually select each cell, thanks in advance


Here is a User Defined Function you can use:

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConcatRange(range, [delimiter])

in some cell.

The delimiter is optional and, if omitted, will default to a <space


==========================
Option Explicit
Function ConcatRange(rg As Range, Optional sDelim As String = " ") As String
Dim v1 As Variant, v2() As Variant, v As Variant
Dim i As Long
v1 = rg
ReDim v2(0 To rg.Count - 1)
i = 0
For Each v In v1
v2(i) = v
i = i + 1
Next v
ConcatRange = Join(v2, sDelim)
End Function
===============================

Wilbur Chua

Concatenate a range of data
 
Hello! I'm not sure if I understand the question, but I think to concatenate a range of data, You just use = A1&B2 and just drag it all over the range you want to concatenate.

I also learned about concatenate function through https://www.efinancialmodels.com/kno...se-2-formulas/. You can check this out.

Hope this helps!


All times are GMT +1. The time now is 06:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com