I have a column where each cell contains a single letter. I need to
consolidate all these letters and form a single string of text in a separate
cell. In other words, a function equivalent to sum() for characters is what
i'm looking for. Concatenate() works only with 34 cells, and creating a "sum"
of different Concatenate() doesn't seem to work fine.

Any smart way to solve my problem?

Thanks, Stefano

Skip CONCATENATE

Just use the ampersand (&), like this example which contatenates cells A1:A40

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14&A1 5&A16&A17&A18&A19&A20&A21&A22&A23&A24&A25&A26&A27& A28&A29&A30&A31&A32&A33&A34&A35&A36&A37&A38&A39&A4 0

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"smaruzzi" wrote:

I have a column where each cell contains a single letter. I need to
consolidate all these letters and form a single string of text in a separate
cell. In other words, a function equivalent to sum() for characters is what
i'm looking for. Concatenate() works only with 34 cells, and creating a "sum"
of different Concatenate() doesn't seem to work fine.

Any smart way to solve my problem?

Thanks, Stefano

Ron,

thanks a lot. I used CONCATENATE() but got a weird behavior once in awhile.
I ended up write this simple routine, which seems to work fine:

Function SumChar(Data As Range) As String
Dim i As Integer
Dim strMeans As String


For i = 1 To Data.Rows.Count
If Data(i) < "" Then
strMeans = strMeans + Data(i)
End If
Next
SumChar = strMeans
End Function

Thanks, Stefano

"Ron Coderre" wrote:

Skip CONCATENATE

Just use the ampersand (&), like this example which contatenates cells A1:A40

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14&A1 5&A16&A17&A18&A19&A20&A21&A22&A23&A24&A25&A26&A27& A28&A29&A30&A31&A32&A33&A34&A35&A36&A37&A38&A39&A4 0

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"smaruzzi" wrote:

I have a column where each cell contains a single letter. I need to
consolidate all these letters and form a single string of text in a separate
cell. In other words, a function equivalent to sum() for characters is what
i'm looking for. Concatenate() works only with 34 cells, and creating a "sum"
of different Concatenate() doesn't seem to work fine.

Any smart way to solve my problem?

Thanks, Stefano

Ciao Stefano,

The only problem with your function is that its result won't be refreshed
whenever you change any of the cells contained in the range passed as
parameter.
What Ron suggested will do it.
--
Stefano Gatto


"smaruzzi" wrote:

Ron,

thanks a lot. I used CONCATENATE() but got a weird behavior once in awhile.
I ended up write this simple routine, which seems to work fine:

Function SumChar(Data As Range) As String
Dim i As Integer
Dim strMeans As String


For i = 1 To Data.Rows.Count
If Data(i) < "" Then
strMeans = strMeans + Data(i)
End If
Next
SumChar = strMeans
End Function

Thanks, Stefano

"Ron Coderre" wrote:

Skip CONCATENATE

Just use the ampersand (&), like this example which contatenates cells A1:A40

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14&A1 5&A16&A17&A18&A19&A20&A21&A22&A23&A24&A25&A26&A27& A28&A29&A30&A31&A32&A33&A34&A35&A36&A37&A38&A39&A4 0

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"smaruzzi" wrote:

I have a column where each cell contains a single letter. I need to
consolidate all these letters and form a single string of text in a separate
cell. In other words, a function equivalent to sum() for characters is what
i'm looking for. Concatenate() works only with 34 cells, and creating a "sum"
of different Concatenate() doesn't seem to work fine.

Any smart way to solve my problem?

Thanks, Stefano