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
and Excel will magically merge the 3 columns A1, A2, A3 together and separate them using the separator you defined.