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 !!!
Bookmarks