How to concatenate adjacent cells in a range without using &?

A

Ark

I would like to know if there is a way to concatenate multiple cells in a row
without having to use concatenate with "," or "&" ?

i.e. is there something like concatenate(A1:F1)?
 
B

Biff

Hi!
is there something like concatenate(A1:F1)?
Click to expand...

No, no built in way to do that. Maybe something like that in the upcoming
version.

Here's a UDF that I found that does it: (don't know the author to
acknowledge)

Function Concatall(rng As Range) As String
Dim cell As Range
For Each cell In rng
Concatall = Concatall & cell.Text & " "
Next
End Function

Put it in a General module.

This concatenates a range and uses a space as a separator. If there are
empty cells in the range they are included in the returned string:

Suppose your range is A1:A5:

A1 = 1
A2 = empty
A3 = 3
A4 = 4
A5 = 5

The formula (using the UDF) would be:

=CONCATALL(A1:A5)

The return would be: 1<space> 3 4 5

You can get rid of the spaces by using this formula:

=SUBSTITUTE(concatall(A1:A5)," ","")

The return would be: 1345

Or, you could get rid of the space AND change the separator to a comma by:

=SUBSTITUTE(TRIM(concatall(A1:A5))," ",", ")

The return would be: 1,3,4,5

Biff
 
G

GRM via OfficeKB.com

Ark said:
I would like to know if there is a way to concatenate multiple cells in a row
without having to use concatenate with "," or "&" ?

i.e. is there something like concatenate(A1:F1)?
Click to expand...

Yes there is a function if you prefer. Syntax is CONCATENATE(A1,B1,C1) not A1:
C1

But you'll find using & a lot quicker
 
G

Gord Dibben

Ark

You will have to use a User Defined Function.

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

Usage is: =concatrange(A1:F1)

Note you can change the comma separator to a space or any combination of
deleimiter.

If you prefer, you could use a macro to do same thing.

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


Gord Dibben Excel MVP
 
R

Ron Rosenfeld

I would like to know if there is a way to concatenate multiple cells in a row
without having to use concatenate with "," or "&" ?

i.e. is there something like concatenate(A1:F1)?
Click to expand...

You could use a UDF.

The one below puts n Spaces between each string. It will handle a contiguous
cell range, non-contiguous cell ranges, or mixed ranges and strings as an
argument. For example:

=setstring(1,A1:D1,G1:H1,"the end")

would be a valid function call.

========================
Function SetString(SpacesBetween As Integer, _
ParamArray rg() As Variant) As String

'by Ron Rosenfeld

Dim c As Variant
Dim i As Long

For i = 0 To UBound(rg)
Select Case VarType(rg(i))
Case Is = vbArray + vbVariant
For Each c In rg(i)
SetString = SetString & Space(SpacesBetween) & Trim(c.Text)
Next
Case Is = vbString
SetString = SetString & Space(SpacesBetween) & Trim(rg(i))
End Select
Next i

SetString = Trim(SetString)

End Function
====================


--ron
 
Top