Concatenation

[Tony]

Is there a way to concatenate contiguous text cells without using multiple
*&*s

For numeric values I can do *=SUM(A1.H1)* , but how do I create a text
string using all the characters of text in A1.H1?

TIA
Tony

 

[Gord Dibben]

Tony

VBA macro to Concatenate selected cells.

Note: no need for the cells to be contiguous.

Also, will skip blanks in the range.

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........

=CONCATENATE(a1,b1,c1,d1,e1,f1,g1,h1)

This will not contain spaces between words however.

Gord Dibben Excel MVP

 

[keepITcool]

there's a concatenate function..

but the programmer must have had a bad pizza.. or a bad hair day..

it's argument is a paramarray.. and it will not process arrays or a
multiarea range. which would have been very usefull...
as it is there's no advantage over &

(and wouldnt it be nice if you could glue the text values of an entire
range into 1 cell?

Or try it with following udf
(DONOT put it in an object module (not in thisworkbook, not in
sheet1...)

Open VBE/Insert Module then copy/paste


OPTION EXPLICIT

Public Function GlueText( _
data As Variant, _
Optional delimiter As String = vbNullString) As String

Dim rArea, rCell, r&, c&, s$
If TypeOf data Is Range Then
For Each rArea In data.Areas
For Each rCell In rArea.Cells
'Note: for ranges the (formatted) Text property is used
If Len(rCell) Then s = s & delimiter & rCell.Text
Next
Next
ElseIf IsArray(data) Then
On Error Resume Next
c = LBound(data, 2) + 1
On Error GoTo 0
If c > 0 Then GoTo TwoDim Else GoTo OneDim

TwoDim:
For r = LBound(data, 1) To UBound(data, 1)
For c = LBound(data, 2) To UBound(data, 2)
If Len(data(r, c)) Then s = s & delimiter & data(r, c)
Next
Next
GoTo TheEnd
OneDim:
For r = LBound(data) To UBound(data)
If Len(data(r)) Then s = s & delimiter & data(r)
Next
Else
s = data
End If
TheEnd:
GlueText = Mid(s, 1 + Len(delimiter))
End Function



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Jim Cone]

And one more way...
'-------------------------------------------------------
'Loads an array with the text values from the selection.
'Uses the Join function to concatenate the array elements.
'Assigns the joined values to the A1 cell.
'Note: the Join function is available only in recent versions of XL.
'Jim Cone - 10/30/2004

Sub PutItTogether()
Dim rngSelect As Range
Dim rngCell As Range
Dim N As Long
Dim lngCount As Long
Dim arrText() As String

If TypeName(Selection) = "Range" Then
Set rngSelect = Selection
lngCount = WorksheetFunction.CountA(rngSelect)

If lngCount > 0 Then
ReDim arrText(1 To lngCount)
For Each rngCell In rngSelect
If Len(rngCell.Text) Then
N = N + 1
arrText(N) = rngCell.Text
End If
Next 'N
Range("A1").Value = Join(arrText)
End If
End If

End Sub
'-------------------------------------------------------
Jim Cone
San Francisco, CA