Ignore blank cells on formula to join text

[Maddy1234]

Hi all,

I am using a UDF from njimack to achieve the following result in Excel 2013.

Column 1	Column 2	Column 3	Formula result
1st	2nd	3rd	1st, 2nd, 3rd
		3rd	3rd

<tbody>
</tbody>

This is his code.

[COLOR=#333333]Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)[/COLOR]Dim Cell As Range


Application.Volatile


For Each Cell In myrange
    If Len(Cell.Value) > 0 Then
        Concatenate_Range = Concatenate_Range & Cell & myDelimiter
    Else: Concatenate_Range = Concatenate_Range
    End If
Next Cell


If Len(myDelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(myDelimiter))

 [COLOR=#333333]End Function[/COLOR]

This gives a fantastic result. However I wanted to improve it a bit. Instead of getting the result as "1st, 2nd, 3rd" I want the last comma to change to a "&".

So the desired result would be this. "1st, 2nd & 3rd" OR "1st & 2nd". You get the gist.

Any help is extremely appreciated. Thank you guys.

 

[Trebor76]

Hi Maddy1234,

Probably would have been easier to reach out to njimack on the thread they answered your original query on, but this should do the job:

Option Explicit
Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)

    Dim Cell As Range
    Dim lngCountNonBlank As Long
    Dim lngItemCount As Long
    
    Application.Volatile
    
    lngCountNonBlank = Evaluate("COUNTA(" & myrange.Address & ")")
    
    If lngCountNonBlank <= 1 Then
        Concatenate_Range = myrange.Value
        Exit Function
    Else
        For Each Cell In myrange
            If Len(Cell.Value) > 0 Then
                lngItemCount = lngItemCount + 1
                If lngItemCount = 1 Then
                    Concatenate_Range = Cell
                ElseIf lngItemCount < lngCountNonBlank Then
                    Concatenate_Range = Concatenate_Range & myDelimiter & Cell
                Else
                    Concatenate_Range = Concatenate_Range & "&" & Cell
                End If
            Else: Concatenate_Range = Concatenate_Range
            End If
        Next Cell
    End If
    
    If Len(myDelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(myDelimiter) + 1)

End Function

Regards,

Robert

 

[Aladin Akyurek]

If your Excel version includes the native TEXTJOIN() function, you can just invoke:

=TEXTJOIN(", ",TRUE,A1:C1)

 

[Rick Rothstein]

If you cannot make use of Aladin's solution (Message #3 ), then give this a try...

[table="width: 500"]
[tr]
	[td]Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)
  Dim Cell As Range
  Application.Volatile
  For Each Cell In myrange
    If Len(Cell.Value) > 0 Then
      Concatenate_Range = Concatenate_Range & myDelimiter & Cell
    Else
      Concatenate_Range = Concatenate_Range
    End If
  Next
  Concatenate_Range = Mid(Concatenate_Range, Len(myDelimiter) + 1)
  If InStr(Concatenate_Range, myDelimiter) Then
    Concatenate_Range = Application.Replace(Concatenate_Range, InStrRev(Concatenate_Range, myDelimiter), Len(myDelimiter), " and ")
  End If
End Function[/td]
[/tr]
[/table]

 

[Maddy1234]

Thanh you for your support mate but for some reason this code would not append the last last date after the "&". I got it to work by using Rick's code.

Hi Maddy1234,

Probably would have been easier to reach out to njimack on the thread they answered your original query on, but this should do the job:

Option Explicit
Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)

    Dim Cell As Range
    Dim lngCountNonBlank As Long
    Dim lngItemCount As Long
    
    Application.Volatile
    
    lngCountNonBlank = Evaluate("COUNTA(" & myrange.Address & ")")
    
    If lngCountNonBlank <= 1 Then
        Concatenate_Range = myrange.Value
        Exit Function
    Else
        For Each Cell In myrange
            If Len(Cell.Value) > 0 Then
                lngItemCount = lngItemCount + 1
                If lngItemCount = 1 Then
                    Concatenate_Range = Cell
                ElseIf lngItemCount < lngCountNonBlank Then
                    Concatenate_Range = Concatenate_Range & myDelimiter & Cell
                Else
                    Concatenate_Range = Concatenate_Range & "&" & Cell
                End If
            Else: Concatenate_Range = Concatenate_Range
            End If
        Next Cell
    End If
    
    If Len(myDelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(myDelimiter) + 1)

End Function

Regards,

Robert

 

[Maddy1234]

I only have Excel 2013 Aladin

If your Excel version includes the native TEXTJOIN() function, you can just invoke:

=TEXTJOIN(", ",TRUE,A1:C1)

 

[Maddy1234]

Rick Rothstein FTW!

 

[Maddy1234]

You Sir are a legend!

With a couple of tweaks I got it to work for my purposes perfectly. Thank you so much!

I was wondering if you could help me better the code even further.

This is the data set I have right now.

Date	1st	2nd	3rd	4th	5th	6th	7th	Weekday OT dates	Weekend OT dates
No of OT hours	0	2	4	1	0	2	0	2nd, 3rd & 4th	6th

<tbody>
</tbody>

The user manually inputs the number of hours worked.(in red)

The expected outcome is in purple. How I achieve this now is really not streamlined. I run 31 IF statements in 31 cells to check whether the OT hours is 0 or not and where its not 0 I would add the date. And at the end I use the UDF you gave to join the text and get the desired result in purple.

IF(C2>0, "1st","") , IF(C3>0, "2nd", "") likewise. You get the idea.

Is there a way you could enhance the UDF code to achieve this result in one go? This is an example data set for Week 1 and 6th and 7th are weekends. I want the result to show weekdays and weekend days separately. For now the dates are formatted as text but I'm more than willing to change it to date format if you need to use Weekday() in excel.

Many thanks in advance.

If you cannot make use of Aladin's solution (Message #3 ), then give this a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)
  Dim Cell As Range
  Application.Volatile
  For Each Cell In myrange
    If Len(Cell.Value) > 0 Then
      Concatenate_Range = Concatenate_Range & myDelimiter & Cell
    Else
      Concatenate_Range = Concatenate_Range
    End If
  Next
  Concatenate_Range = Mid(Concatenate_Range, Len(myDelimiter) + 1)
  If InStr(Concatenate_Range, myDelimiter) Then
    Concatenate_Range = Application.Replace(Concatenate_Range, InStrRev(Concatenate_Range, myDelimiter), Len(myDelimiter), " and ")
  End If
End Function[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

This is the data set I have right now.

Date	1st	2nd	3rd	4th	5th	6th	7th	Weekday OT dates	Weekend OT dates
No of OT hours	0	2	4	1	0	2	0	2nd, 3rd & 4th	6th

<tbody>
</tbody>

Are the labels 1st through 7th always exactly as shown? In other words, is the label in cell B1 always going to be "1st" (meaning cell B1 will always correspond to a Monday) and the label in cell C1 always going to be "2nd" (meaning cell C1 will always correspond to a Tuesday) and so on through to cell H1 which always display"7th" (meaning cell H1 will always correspond to a Sunday)?

 

[Rick Rothstein]

This is the data set I have right now.

Date	1st	2nd	3rd	4th	5th	6th	7th	Weekday OT dates	Weekend OT dates
No of OT hours	0	2	4	1	0	2	0	2nd, 3rd & 4th	6th

<tbody>
</tbody>

Are the labels 1st through 7th always exactly as shown? In other words, is the label in cell B1 always going to be "1st" (meaning cell B1 will always correspond to a Monday) and the label in cell C1 always going to be "2nd" (meaning cell C1 will always correspond to a Tuesday) and so on through to cell H1 which always display"7th" (meaning cell H1 will always correspond to a Sunday)?

Assuming the answer to my above question is "yes", then give this completely revised function a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ConcatOT() As String
  Dim Cell As Range, OTrng As Range
  Const Delimiter = ", "
  Application.Volatile
  Set OTrng = Intersect(Application.Caller.EntireRow, IIf(Application.Caller.Column = 9, Columns("B:F"), Columns("G:H")))
  For Each Cell In OTrng
    If Cell.Value > 0 Then ConcatOT = ConcatOT & Delimiter & Intersect(Cell.EntireColumn, Rows(1))
  Next
  ConcatOT = Mid(ConcatOT, Len(Delimiter) + 1)
  If InStr(ConcatOT, Delimiter) Then
    ConcatOT = Application.Replace(ConcatOT, InStrRev(ConcatOT, Delimiter), Len(Delimiter), " and ")
  End If
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Note that the function named has changed to ConcatOT and that it takes no arguments. Simply put this in cell I2...

=ConcatOT()

and copy it across to cell J2, then copy them both down to the end of your data.