Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 17th 12, 02:04 PM posted to microsoft.public.excel.programming
jt jt is offline
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 18
Default concatenate a range of data

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

  #2   Report Post  
Old March 17th 12, 02:30 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2010
Posts: 143
Default 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
  #3   Report Post  
Old March 17th 12, 03:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default 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

  #4   Report Post  
Old March 17th 12, 06:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,549
Default 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



  #5   Report Post  
Old March 17th 12, 07:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default 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
===============================


  #6   Report Post  
Old June 24th 21, 01:31 PM
Member
 
First recorded activity by ExcelBanter: Jun 2021
Posts: 47
Default 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!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate range (without UDF) Greg Lovern Excel Worksheet Functions 6 September 17th 09 05:36 PM
Concatenate a Range of Cells Bob Stearns Excel Programming 4 January 27th 06 07:19 AM
Concatenate a range King Excel Worksheet Functions 3 March 11th 05 10:10 PM
Concatenate a strings range. y Excel Programming 3 April 26th 04 04:30 AM
How to concatenate a range? Nathan Gutman Excel Programming 2 December 18th 03 06:34 PM


All times are GMT +1. The time now is 03:07 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017