CODEWITHSUNDEEP
excel
Robert Ban
Robert Ban

Reputation: 23

Excel concatenate unknown number of cell values without VBA

I need to send out an Excel file to 3rd party to capture data and I must not use VBA.

The user must capture at least one string and by default there is space to capture up to 10 strings, say in the range D23:D32. Row 33 is an empty row. From row 34 onwards other data must be captured. I can define a name for D33 or D34.

If the user needs to capture more than 10 strings he can insert additional rows.

Now I need to concatenate the strings the user has captured separating them by a semicolon. To clarify what I mean. If the user was not able to insert rows this formula would do it:

=D23 & IF(D24="","",";"&D24) & IF(D25="","",";"&D25) & IF(D26="","",";"&D26)&IF(D27="","",";"&D27)&IF(D28="","",";"&D28)&IF(D29="","",";"&D29)&IF(D30="","",";"&D30)&IF(D31="","",";"&D31)&IF(D32="","",";"&D32)

Is there a way to include the rows the user might have added?

Upvotes: 2

Views: 5848

Answers (2)

whiskeychief
whiskeychief

Reputation: 93

The method suggested,

=LOOKUP(2,1/(RIGHT(Table1[concatenation],1)<>";"),Table1[concatenation])

is a very good one. There is another method suggested by Alan Wyatt for an unknown number of cell values without Visual Basic/VBA. His method is adaptable to the situation where you have one or more groups of cells to be concatenated, which is handy.

Let Column A contain a unique identifier that groups the cells.

Let Column B contain the items to be concatenated.

A        B
--------|----------
Alice    The quick
Alice    brown
Alice    fox
Alice
Bruce    Fourscore
Bruce    and seven
Bruce    years ago
Bruce
Bruce
Carol    Yankee
Carol    doodle
Carol    dandy

Column C is used as a helper column. In Column C, place:

C1: = B1

C2: = IF(A2=A1,C1 & ", " & B2, B2)

Copy this downward as far as required.

Column D is the result column. In Column D, place:

D1: =IF(A1=A2,"",C1)

Copy this downward as far as required. (Thanks to user @Krylose for this.)

Column D will show either the concatenation, or blanks, which you can ignore.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

A simple VBA User Defined Function would provide a much simpler and muchmore robust solution.

If you can ensure that your users will only insert rows at the bottom of the list, you could use a Table and a hidden column.

D22:  Data
E22: Concatenation

E23: =D23
E24: =CONCATENATE(E23,";",D24)

Fill down E24 to the bottom of the table.

Formula to show the result (in D19 in the screen shot below):

=LOOKUP(2,1/(RIGHT(Table1[concatenation],1)<>";"),Table1[concatenation])

Because this is a Table, the formula will extend when rows are inserted and additional entries made. However, the method will require discipline on the part of the user as the structure can be easily messed up.

Here is what it might look like, with Column E NOT hidden, and with the formulas showing in adjacent cells:

enter image description here

On the other hand, a simple user defined function could be devised to return either all of the data from D23 to the first blank row, concatenated; or could select the range in some other fashion if there might be blanks within the range.

By blanks within the range, I mean that there might be entries in the range D23:D32 that are blank, with valid data below that. Eg. Valid data in D23, D24, and D30, with nothing in D25:D29

Here is what the UDF might look like:

Function ConcatLines(rg As Range, Optional Delimiter As String = " ") As String
Dim C As Range
For Each C In rg
    ConcatLines = ConcatLines & Delimiter & C
Next C
ConcatLines = Mid(ConcatLines, 2)
End Function

And the formula to display the result, from D23 to the first blank row below it, would be array-entered:

=ConcatLines(OFFSET($D$23,0,0,MATCH(TRUE,ISBLANK($D$23:$D$100),0)-1),";")

To array-enter a formula, hold down ctrl-shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

Upvotes: 1

Related Questions