concatenate

[Kandi25]

--------------------------------------------------------------------------------

Dear All,

I have to put words / expressions from several cells (each word / expression is in one cell) into one cell, next to each other, and to separate them with sign '+'.

I've tried to do it with function 'concatenate', but it allows max. 30 units in a string, and if I put a sign among each word then it reduces to 15.
And in some cases I should do a string with about 50 words.

Do you have any ideas?
If you need other info to see clearer, ask!!!

Thank you in advance,
Kandi25

[Domski]

A UDF like this would do the trick:

Public Function ConcatAll(rngConcat As Range, Optional strSpacer As String) As String

    Dim rngLoopRange As Range

    For Each rngLoopRange In rngConcat
        If ConcatAll = "" Then
            ConcatAll = rngLoopRange
        Else
            ConcatAll = ConcatAll & strSpacer & rngLoopRange
        End If
    Next rngLoopRange

End Function

Use it like this: =CONCATALL(A1:M1,"+")

Dom

[Kandi25]

Oh, you are great!!! ))
It works!
Thanks a lot!
Now I am just trying to do that the column was in the same excel worksheet, to which I referred in the function concatall(A1:A220,"+"), and I don't need this column anymore. But of course as soon as I delete it, also my created cell - where all the items are with '+' - disppears.....
But I think i can do it.

I am really grateful to your quick and clever help!!!

have a nice day!
Kandi25

[Kandi25]

Oh great!!! I could do the reference from another document, so that is great now!