How to get around excel concatenation limit

[larissalle]

How do I get around the limit that excel has on the number of cells that can be included in the concatenation function? Seems to max out at 15 columns. I need to concatenate 20 columns, one to 10 characters each, mix of strings and numbers.

I tried using VBA to do a concatenation upon activating the worksheet, but didn't work as some columns were strings and some numbers and I got back a type mismatch. I also need a dash between the numbers.

Example:

ColA ColB ColC ColD
Row A 0.25 0.18 0.13 New
Row B 0.18 0.13 Existing

Concatenated fields should result in:

Row A 0.25-0.18-0.13 New
Row B 0.18-0.13 Existing

 

[Chance1234]

how about doing half in one cell, the halff in another cell then using the concatenate on those two cells to join them together ?

or do the 15 , copy paste values only then do another concatenate on the memaining 5 plus the 15 you jucst copied pasted

 

[ilses]

The maximum number of functions for arguments for Excel2000 (don't know about '97 or XP) is 30.

Easiest thing to to is create an empty string and add cell content to the string. The sub below uses this approach. I'm not too happy 'bout all the ifs used, but ok. The msgbox shows the output string strOut for a row, then the string is set to "" again after which the strOut is filled with the stuff from the next row (don't know where you want the resulting strings to go, so ...)

Please note: The sub assumes that you have precisely 20 columns as stated in your post. It also presumes that column A is the column with the most rows. If that is not the case the maxRows will have to be determined in another way.

Sub test()

Dim i As Integer 'counter for the rows
Dim j As Integer 'counter for the columns
Dim maxRows As Integer
Dim strOut As String

maxRows = Range("A65535&quot.End(xlUp).Row
strOut = ""

For i = 1 To maxRows
For j = 1 To 20
If j = 1 Then
strOut = strOut & Cells(i, j)
Else
If Cells(i, j) <> &quot;&quot; Then
If IsNumeric(Cells(i, j)) Then
If IsNumeric(Cells(i, j - 1)) Then
strOut = strOut & &quot;-&quot; & Cells(i, j)
Else
strOut = strOut & &quot; &quot; & Cells(i, j)
End If
Else
strOut = strOut & &quot; &quot; & Cells(i, j)
End If
End If
End If
Next j
MsgBox strOut
strOut = &quot;&quot;
Next i

End Sub

IS