Excel Tips and Formulas

The Pipe Formula

Excel PIPE Formula

Concatenate a Range with "|" to import classes
PIPE FORMULA

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
Next

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:

=CONCATRANGE(A1:A100,"|")

Share This: