Concatenate Rows with limited Characters counts

[nirmal74]

Hi All,

I hope my title is clear and appreciate all the help rendered.

I am not sure what would be the best method either a vba or simple formula within excel would able to achieve the results I wanted.

The task I would like to achieve is to be able to group certain series numbers and concatenate it to a maximum of 1,000 characters. The series numbers comprise of 8 digits. I need to differentiate the series numbers with a comma and no spaces. The source series will be copied from a database and paste into column A. Sometimes it could exceed to more than 10k rows.

So, column A will consists of raw series numbers e.g. 23200000

Column B with addition to a comma e.g. 23200000,

Column C consists of concatenated of column B values but to max of 1000 characters with complete series numbers (8 digits).

See sample file.

Column C needs to be automatically filled once the raw data are pasted in column A. How I do I achieve the said results?

Thanks again for your time and help.

 

[BBD]

Hi Nirmal,

There is no formula in excel which you can use to acheive it. But here is a UDF which will help you do it.

Function Cuncat(rng As Range, Sep As String, Optional Max_Len As Long, Optional Cnt As Long) As String
Dim Cell As Range, i As Long
Application.volatile
If Cnt = 0 Then Cnt = 1
For i = 1 To Cnt
For Each Cell In rng

    If Cell = rng.Cells(1, 1) Then Cuncat = ""
    If Len(Cuncat) + Len(Sep) + Len(Cell) > Max_Len And Not Max_Len = 0 Then
    Set rng = Cell.Cells(1, 1).Resize(rng.Rows.Count - Cell.Row + rng.Row, 1)
    Exit For
 Else
   If Not Cuncat = "" Then
       Cuncat = Cuncat & Sep & Cell.Value
   Else
       Cuncat = Cell.Value
   End If
End If

If Cell.Address = rng.Cells(rng.Rows.Count, 1).Address And i < Cnt Then
Cuncat = ""
GoTo Finish:
End If
Next Cell
Next i
Finish:
End Function

Put the above code in a module in the file where you have the data. Once done, you can use the below syntax :

=cuncat(Range, Separator, Maximum Length(Optional),Set Count (optional))

Below is the file which has examples to use the formula. (Highlighted in Green)

Cheers,
BD

 

[nirmal74]

BD,
Thanks for your reply and code. I am sorry I did not post my reply any earlier due to work commitments.
Would it be possible to have the column C to auto-generate once data are pasted in column A ?

Thanks again and appreciate it.

 

[BBD]

Hi nirmal74,

The trick here would be to use a dynamic range and use this named range as a source data for the UDF created. Below is an example to do it. If you add more data, the formula will pick up automatically.

Cheers,
BD

 

[nirmal74]

Hi nirmal74,

The trick here would be to use a dynamic range and use this named range as a source data for the UDF created. Below is an example to do it. If you add more data, the formula will pick up automatically.

Cheers,
BD

Wow, thats fantastic!
Thanks for the help BD. Appreciate the help.
Best regards,