Concatenate 5 cells into a single But including front zeroes or all zeros, e.i 000

Hi,

Need some help, might someone tell me a snipped to add to the code?

Im trying to concatenate 5 cells from Range R22:V22 to Single cell DU22 but adding a dash between.

The VBA code it does the job as long the string are single number with front zero, now i need to do the same but this time there are five cells and each one include 3 digits, Example : 000 001 010 121 110

When are three positive digits is not a problem, but when such digits Include leading zero it wont concatenate correct.
When I place the Formula - Concat(R22:V22) in Cell DU22 and DU23 as well, it won't display the leading zeroes at front or if the string is triple zero it just display single zero.

Example:
--->>>C O L U M N S
______R___S___T____U___V__
Row
22___010_000_010_112_212 -> DU22 ->Display --> 10-0-10-112-212
23___000_120_100_120_121 -> DU23 ->Display --> 0-120-100-120-121

This is the code I'm Using:

Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString) As String
'Created by TigerAvatar at www.excelforum.com, September 2012
'Purpose is to concatenate many strings into a single string
'Can be used with arrays, range objects, and collections
    
    Dim DataIndex As Variant    'Used to loop through arrays, range objects, and collections
    Dim strResult As String     'Used to build the result string
    
    'Test if varData is an Array, Range, or Collection
    If IsArray(varData) _
    Or TypeName(varData) = "Range" _
    Or TypeName(varData) = "Collection" Then
        
        'Found to be an, array, range object, or collection
        'Loop through each item in varData
        For Each DataIndex In varData
            'Check if the item isn't empty and if so add it to the result with the delimiter
            If Len(DataIndex) > 0 Then strResult = strResult & sDelimiter & DataIndex
        Next DataIndex
        
        'Correct strResult to remove beginning delimiter
        strResult = Mid(strResult, Len(sDelimiter) + 2)
        
    Else
        'Found not to be an array, range object, or collection
        'Simply set the result = varData
        strResult = varData
    End If
    
    'Output result
    ConcatAll = strResult
    
End Function

Thanks !!!

Hi Crooza,

No is displaying the same ...

Example:
--->>>C O L U M N S
______R___S___T____U___V__
Row
22___010_000_010_112_212 -> DU22 ->Display --> 10-0-10-112-212

Hi,

Yes is working!!

the Call function was ConCatAll=

Excel cell had ConCatal (from previous code tested)

Thank you all !!

Crooza, Yujin, Jolivanes and sktneer !!

Regards

If you don't want front zeroes to vanish, you need to format the digits with Format function.

If Len(DataIndex) > 0 Then strResult = strResult & sDelimiter & Format(DataIndex, "000")

Hi Yujin,

Is the same output my friend..

Example:
--->>>C O L U M N S
______R___S___T____U___V__
Row
22___010_000_010_112_212 -> DU22 ->Display --> 10-0-10-112-212

Hi Jujin,

Contrary, I need the Front zeros and or all zeros (if in cell contents)

the first thing i did was the formatting 000-000-000-000-000

Try this

Sub AAAAA()
Dim a As String, i As Long, j As Long
    For i = 1 To Cells(Rows.Count, 18).End(xlUp).Row
        a = CStr(Cells(i, 18))
            For j = 19 To 22
                a = a & "-" & CStr(Cells(i, j))
            Next j
        Cells(Rows.Count, 125).End(xlUp).Offset(1) = a
    Next i
End Sub