Concatenate Range of Cells - Empty and Digits

[ststern45]

Hello everyone,

Excel 2010 version of Excel

I searched and found the following code at "Extendoffice". See Below
It does everything required but the formula will not add leading zero (if required) in the concatenated set of 4 digit values from 0 through 9 even if I format the cells as "0000".

Each cell range contains 16 cells.
Each cell range can only contain 4 digits 0 through 9 and they can repeat.

Thank you in advance!!

Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice
For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & ""): Next
Concatenatecells = Left(nn, Len(nn) - 1)
End Function

 

[Joe4]

Formatting cells only changes their appearance, not their contents.

There are a few ways you can ensure your entries are exactly 4 digits long:

1. Convert the entry to a number (value), then apply the FORMAT function in VBA to it, i.e.
Format(yourString + 1,"0000")
See: MS Excel: How to use the FORMAT Function with Strings (VBA)

2. Concatenated 4 "0"s on to the front, and then take the 4 right-most characters, i.e.
Right("0000" & yourString,4)