Help! Where's my Zeros?

[AJev]

Hello All - I'm at my wits end and I'm hoping someone can help me...

I have a column of numbers that I used a custom number format to put a leading zero on the front of all the numbers. I then used the function shown below (which I found online) to pull all the numbers out and put them in one cell with a ";" as a separator. The function works great but I am losing my zeros, can someone please tell me how to get them back?

Function CombineColumn(myRange As Range)

    Dim cell As Range
    For Each cell In myRange
        If Len(cell) > 0 Then
            CombineColumn = CombineColumn & cell.Value & ","
        End If
    Next cell
    CombineColumn = Left(CombineColumn, Len(CombineColumn) - 1)
    
End Function

Any help is GREATLY appreciated...

[JosephP]

the cell value is independent of format so you need the text property

Function CombineColumn(myRange As Range)

    Dim cell As Range
    For Each cell In myRange
        If Len(cell) > 0 Then
            CombineColumn = CombineColumn & cell.Text & ","
        End If
    Next cell
    CombineColumn = Left(CombineColumn, Len(CombineColumn) - 1)
    
End Function

although that could be slow if you have lots of data

[AJev]

The column has 2700 rows, each cell contains an 8 digit number that I need to be a 9 digit number begining with a zero. I used the "custom" number format to make the field a nine digit number starting with a zero. When I look at the number, it shows the zero but if I hit F2, the number still shows without the zero and when I run my function, it is still showing without the zero.

I was hoping there was either another way to format the number to include the zero or perhaps a way to setup the function so it didn't drop them...

[JosephP]

or perhaps a way to setup the function so it didn't drop them...

that's what my code is

[AJev]

IT WORKED!!!!!!!!!!! YOU ARE THE BEST - I can't thank you enough!!!!

[AJev]

Oh my, I really have been looking at this way too long, I didn't even see the change in the function, Sorry.

Thank you sooooo much!!!