Concatenate Cell Range - Empty Cells

[ststern45]

Hello,
I have a range of cell from A1 through F1
Values in this range can vary from 00 through 99
Cell G1 contains the formula that concatenates all the values from range A1 through F1
Random cells in A1 through F1 may be empty.
Each of the values will be separated by a coma.

I found the following code for a Functions that works but I'm looking for code that will keep the 1st zero when concatenating the range
If a cell contains any value for 00 though 09 I would like to keep the 1st zero.

Function ConcatenateButBlank(selectArea As Range) As String
For Each oneCell In selectArea: finalResult = IIf(oneCell = "", finalResult & "", finalResult & oneCell & ","): Next
ConcatenateButBlank = Left(finalResult, Len(finalResult) - 1)
End Function

Thank you in advance!!

 

[Rick Rothstein]

I think this replacement for the function you are now using should do what you want...

Function ConcatenateButBlank(selectArea As Range) As String
  ConcatenateButBlank = Replace(Application.Trim(Replace(" " & Join(Evaluate("TEXT(" & selectArea.Address & ",""00"")")) & " ", " 00 ", " ")), " ", ",")
End Function

 

[bebo021999]

May be:

Function ConcatenateButBlank(selectArea As Range) As String
Dim val As String, st As String, onecell As Range
Application.Volatile
For Each onecell In selectArea
    If IsEmpty(onecell) Then
        val = "00"
    Else: val = Format(onecell, "00")
    End If
    If st = "" Then
        st = val
    Else
        st = st & "-" & val
    End If
Next
ConcatenateButBlank = st
End Function

 

[Peter_SSs]

Another one you could also try

Function ConcatenateButBlank(selectArea As Range) As String
  ConcatenateButBlank = Join(Filter(Evaluate(Replace("if(#="""",""|"",if(len(#)=1,0&#,#))", "#", selectArea.Address)), "|", 0), ",")
End Function

 

[ststern45]

Thank you everyone for your help
Peter. your code worked.
I did not try Ricks or bebo's suggestions
Sorry for the late thank you to all.
Very much appreciated

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

I did not try Ricks or bebo's suggestions

That may not encourage them to try to help you if you have further questions.
Remember that people are contributing their time to try to help you for no payment.

 

[ststern45]

Ricks code returns "VALUES" in the cell
bebo's code works but adds dashes for blanks cells
Peter. Your code works. The only question is when I reopen the Excel file I need to go into each cell and select the Enter key. Is there a way this Function will work when I reopen the Excel file?
Thank you all again
Sorry I did not respond to all the help.

01--07-12--27

01

07

12

27

 

[Peter_SSs]

The only question is when I reopen the Excel file I need to go into each cell and select the Enter key.

Are you sure that your 'Calculation' is not set to Manual? Have a look at the Formulas ribbon tab -> Calculation section -> Calculation Options

 

[bebo021999]

Ricks code returns "VALUES" in the cell
bebo's code works but adds dashes for blanks cells
Peter. Your code works. The only question is when I reopen the Excel file I need to go into each cell and select the Enter key. Is there a way this Function will work when I reopen the Excel file?
Thank you all again
Sorry I did not respond to all the help.

01--07-12--27

01

07

12

27

With this lines of code:

 If IsEmpty(onecell) Then
        val = "00"

So supprise if it did not work!

 

[Rick Rothstein]

Ricks code returns "VALUES" in the cell

My code works fine when I test it, so I am not sure why you are getting a #VALUE! error.