Hi!

is there something like concatenate(A1:F1)?

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

"Ark" wrote in message
...
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)?