Hi all,
I am using a UDF from njimack to achieve the following result in Excel 2013.
<tbody>
</tbody>
This is his code.
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.
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.
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.