Alun Rowe

ramblings about cars, tech, web development and more. BOTB Winner in 2010

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!

Previous

HP Photosmart Printer drops out using BT Homehub 3

Next

Turn a SimpleXML object into a PHP array

4 Comments

  1. Mike

    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. David C

    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. Alejandro Otero

    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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress & Theme by Anders Norén