CODEWITHSUNDEEP
arraysexcelconcatenation
peterc
peterc

Reputation: 55

CONCATENATE array

I'd like to CONCATENATE some columns and between them there has to be this. ";\" The problem is that different rows have different numbers of columns. So for example:

      A             B             C

1  Image1.jpg     Image2.jpg    Image3.jpg
2  Image1.jpg     Image2.jpg
3  Image1.jpg

If I CONCATENATE the three columns the third will look like this Image1.jpg;\;\

Is there an option to CONCATENATE the columns only if it's not empty?

Upvotes: 0

Views: 6628

Answers (3)

pnuts
pnuts

Reputation: 59432

Maybe: 

=IF(B1="",A1,IF(C1="",A1&";\"&B1,A1&";\"&B1&";\"&C1))

If you have very many columns your best bet may be to accept the surplus at the end in the first instance and, separately, trim it off.

Up to 10 at a time (no intervening blanks) but in two steps:

  1. =A1&";\"&B1&";\"&C1&";\"&D1&";\"&E1&";\"&F1&";\"&G1&";\"&H1&";\"&I1&";\ "&J1&";\" in say K1 and copied down to suit, then select ColumnK, Copy, Paste Special, Values over the top.
  2. =LEFT(K1,LEN(K1)-2*(COUNTBLANK(A1:J1)+1)) in L1 and copied down to suit.

Upvotes: 1

Tarik
Tarik

Reputation: 11209

I assume the columns containing data are A,B and C.

In column D add the following formula:

=IF(A1="",IF(B1="","",B1),IF(B1="",A1,A1&";\"&B1))

In column E add the following formula to get the final result:

=IF(D1="",IF(C1="","",C1),IF(C1="",D1,D1&";\"&C1))

One shot formula:

=IF(IF(A1="",IF(B1="","",B1),IF(B1="",A1,A1&";\"&B1))="",IF(C1="","",C1),IF(C1="",IF(A1="",IF(B1="","",B1),IF(B1="",A1,A1&";\"&B1)),IF(A1="",IF(B1="","",B1),IF(B1="",A1,A1&";\"&B1))&";\"&C1))

We are handling 8 possible combinations of values being blank or not for three columns (2 to the power of 3). The above IF statements reflect that logic. I would personally write a VBA function instead of using such a lengthy formula.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Here is a VBA based solution. Enter the following UDF in a standard module:

Option Explicit

Public Function Konkatenate(rIn As Range) As String
    Dim r As Range, s As String
    For Each r In rIn
        s = r.Text
        If s <> "" Then
            Konkatenate = Konkatenate & ";\" & s
        End If
    Next r
    Konkatenate = Mid(Konkatenate, 3)
End Function

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=Konkatenate(A1:Z1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

enter image description here

Upvotes: 1

Related Questions