Ignore blank cells on formula to join text

M

Maddy1234

New Member
Joined
Mar 9, 2018
Messages
24
Hi all,

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

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Formula result[/TD]
[/TR]
[TR]
[TD]1st[/TD]
[TD]2nd[/TD]
[TD]3rd[/TD]
[TD]1st, 2nd, 3rd[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3rd[/TD]
[TD]3rd[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is his code.
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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:

Code: 
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
 
Upvote 0
If you cannot make use of Aladin's solution (Message #3 ), then give this a try...
Code: 
[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]
 
Upvote 0
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.
Trebor76 said:
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:

Code: 
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
Click to expand...
 
Upvote 0
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.

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]1st[/TD]
[TD]2nd[/TD]
[TD]3rd[/TD]
[TD]4th[/TD]
[TD]5th[/TD]
[TD]6th[/TD]
[TD]7th[/TD]
[TD="align: center"]Weekday OT dates[/TD]
[TD="align: center"]Weekend OT dates[/TD]
[/TR]
[TR]
[TD]No of OT hours[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2nd, 3rd & 4th[/TD]
[TD]6th[/TD]
[/TR]
</tbody>[/TABLE]







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.
Rick Rothstein said:
If you cannot make use of Aladin's solution (Message #3 ), then give this a try...
Code: 
[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]
Click to expand...
 
Upvote 0
Maddy1234 said:
This is the data set I have right now.

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]1st[/TD]
[TD]2nd[/TD]
[TD]3rd[/TD]
[TD]4th[/TD]
[TD]5th[/TD]
[TD]6th[/TD]
[TD]7th[/TD]
[TD="align: center"]Weekday OT dates[/TD]
[TD="align: center"]Weekend OT dates[/TD]
[/TR]
[TR]
[TD]No of OT hours[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2nd, 3rd & 4th[/TD]
[TD]6th[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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)?
 
Upvote 0
Rick Rothstein said:
Maddy1234 said:
This is the data set I have right now.

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]1st[/TD]
[TD]2nd[/TD]
[TD]3rd[/TD]
[TD]4th[/TD]
[TD]5th[/TD]
[TD]6th[/TD]
[TD]7th[/TD]
[TD="align: center"]Weekday OT dates[/TD]
[TD="align: center"]Weekend OT dates[/TD]
[/TR]
[TR]
[TD]No of OT hours[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2nd, 3rd & 4th[/TD]
[TD]6th[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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)?
Click to expand...
Assuming the answer to my above question is "yes", then give this completely revised function a try...
Code: 
[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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
VBA to perform Text Diff (but with >2500 Char per cell)
Replies
0
Views
294
potatoooooooo
P
A
  • Question Question
Combine text from different cells but apply different formatting
Replies
1
Views
1K
attikuz
A
S
  • Solved
How to use a ComboBox with autocomplete and search as you type, but ignore blank formula or empty cells.
2
Replies
10
Views
5K
sijpie
sijpie
T
  • Question Question
UDF to reformat hierarchical numbering for sorting
Replies
5
Views
3K
tjdickinson
T
N
  • Question Question
Delete not necessary column and rearrange column
Replies
1
Views
644
SpillerBD
S

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top