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!

One thought 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!

Leave a Reply

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