Excel Tips and Formulas

The Pipe Formula

Excel PIPE Formula

Concatenate a Range with "|" to import classes

Function ConCatRange(Rng As Range, Delim As String) As String
'Usage: =CONCATRANGE(A1:A20, ",")
Dim Cell As Range
Dim sbuf As String

For Each Cell In Rng
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & Delim

ConCatRange = Left(sbuf, Len(sbuf) - Len(Delim))
End Function

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

In a nutshell, the function is used like so:


Share This: