Excel/VBA equivalent of the PHP Implode function

On a recent data import between Xero and OnePageCRM I needed to turn multiple column address into an address list into a single line.  For this I needed the PHP implode function so I wrote one for Excel.

The data was structured like this:

And I needed this:

My immediate faction was to use the implode function that I use in PHP everyday but it appears to be missing in Excel so I quickly wrote one.

To add this code you need to do the following:

  1. Open your work book
  2. Press ALT + F11 and the code window should appear
  3. Right click on VBA project and choose INSERT then MODULE
  4. Copy paste the following code:

 

Function IMPLODE(Rng As Range, Sep As String)
Dim TEMP As String
For Each Cell In Rng
If Cell.Value = "" Then
Else
TEMP = TEMP & Cell.Value & Sep
End If
Next Cell
TEMP = Left(TEMP, Len(TEMP) - Len(Sep))
IMPLODE = TEMP
End Function

Now in your worksheet go to the cell you want the combined field to be added to and type

=IMPLODE(A1:A3, ",");

and Excel will magically merge the 3 columns A1, A2, A3 together and separate them using the separator you defined.

Enjoy!

4 thoughts on “Excel/VBA equivalent of the PHP Implode function

  1. Thank you so much!!!! I was just working on a project and I thought, “Is there a way to use IMPLODE in Excel?” and you were the first to come up on a search. just made my life way easier Mister!

  2. Nice.

    Using ‘,’ as the separator and tidying up at the start and end like


    ="in ('"&implode(YourRange,"','")&"')"

    (there’s some single quotes in there that are hard to see)
    gives a list which pastes into SQL
    e.g.

    in ('Tom','Dick','Harry')

  3. You can add some lines there, making this function useful with blank cells also.

    Function IMPLODE(Rng As Range, Sep As String)
    Dim TEMP As String
    TEMP = “”
    For Each Cell In Rng
    If Cell.Value = “” Then
    Else
    TEMP = TEMP & Cell.Value & Sep
    End If
    Next Cell
    If TEMP = “” Then
    Else
    TEMP = Left(TEMP, Len(TEMP) – Len(Sep))
    End If
    IMPLODE = TEMP
    End Function

Leave a Reply

Your email address will not be published. Required fields are marked *