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:
- Open your work book
- Press ALT + F11 and the code window should appear
- Right click on VBA project and choose INSERT then MODULE
- 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!
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!
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')
AlunR
Cheers! Always happy to receive hints back myself 🙂
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
Raj
Great!! Thank you man. This was super easy and I am glad i found this in google search so easily!! Works as expected and your instructions & steps were very straightforward. Thanks again.
John
No longer needed – excel has textjoin now