Concatenate eliminating blank cells

  • Thread starter Thread starter Neil G
  • Start date Start date
N

Neil G

I have a column of 8 cells to concatenate, the last 4 of which may or may
not be blank

Example: (Rows 41-48)
BQ (Column)
Petra
Joe
Ivan
Fred (FN)
Bob (FX)
Graham (FX)
(Blank cell 47)
(Blank cell 48)
Formula used: =CONCATENATE(BQ41,", ",BQ42,", ",BQ43,", ",BQ44,", ",BQ45,",
",BQ46,", ",BQ47,", ",BQ48)

Produces
Petra, Joe, Ivan, Fred (FN), Bob (FX), Graham (FX), ,

Realistically any cell could be blank, BUT filled cells will always be
uppermost (the result of ranking) so how can I concatenate these cells to
eliminate blanks?
I'd like it to appear
Petra, Joe, Ivan, Fred (FN), Bob (FX), Graham (FX)
- eliminate trailing comma

TIA
Neil
 
Quite klunky, and there could be better alternatives, but
this seems to work ..

Try :

=IF(COUNTA(BQ41:BQ48)<>0,CHOOSE(COUNTA
(BQ41:BQ48),BQ41,
BQ41&", "&BQ42,


BQ41&", "&BQ42&", "&BQ43,


BQ41&", "&BQ42&", "&BQ43&", "&BQ44,

BQ41&", "&BQ42&", "&BQ43&", "&BQ44&", "&BQ45,

BQ41&", "&BQ42&", "&BQ43&", "&BQ44&", "&BQ45&", "&BQ46,

BQ41&", "&BQ42&", "&BQ43&", "&BQ44&", "&BQ45&", "&BQ46&", "
&BQ47,
BQ41&", "&BQ42&", "&BQ43&", "&BQ44&", "&BQ45&", "&BQ46&", "
&BQ47&", "&BQ48) ,"")

--

The above uses COUNTA() to return the number of non blank
cells in the range BQ41:BQ48, with CHOOSE() using the
value returned by the COUNTA() to return the appropriate
(evaluated) concatenated string.

The If() ensures that if the range BQ41:BQ48 is empty,
"" is returned

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

Neil G said:
I have a column of 8 cells to concatenate, the last 4 of which may or may
not be blank

Example: (Rows 41-48)
BQ (Column)
Petra
Joe
Ivan
Fred (FN)
Bob (FX)
Graham (FX)
(Blank cell 47)
(Blank cell 48)
Formula used: =CONCATENATE
Click to expand...
(BQ41,", ",BQ42,", ",BQ43,", ",BQ44,", ",BQ45,",
 
Hi

=BQ41 & ", " & BQ42 & ", " & BQ43 & ", " & BQ44 & IF(BQ45="","",", ") & BQ45
& IF(BQ46="","",", ") & BQ46 & IF(BQ47="","",", ") & BQ47 & IF(BQ48="","",",
") & BQ48

It'll be easy to modify the formula so, that any cell except 1st in range
can be empty - simply replace according ", " string with check expression.
When ANY cell in range can be blank, it isn't so easy anymore - every check
has to include the check for whole previous string, resulting with 8 cells
to check to a huge formula like

=BQ41 & IF(OR(BQ41="",BQ42=""),"",", ") & BQ42 & IF(OR(BQ41 &
BQ42="",BQ43=""),"",", ") & BQ43 & IF(OR(BQ41 & BQ42 &
BQ43="",BQ44=""),"",", ") & BQ44 & IF(OR(BQ41 & BQ42 & BQ43 &
BQ44="",BQ45=""),"",", ") & BQ45 & IF(OR(BQ41 & BQ42 & BG43 & BQ44 &
BQ45="",BQ46=""),"",", ") & BQ46 & IF(OR(BQ41 & BQ42 & BG43 & BQ44 & BQ45 &
BQ46="",BQ47=""),"",", ") & BQ47 & IF(OR(BQ41 & BQ42 & BG43 & BQ44 & BQ45 &
BQ46 & BQ47="",BQ48=""),"",", ") & BQ48
 
Arvi Laanemets said:
=BQ41&", "&BQ42&", "&BQ43&", "&BQ44&IF(BQ45="","",", ")&BQ45
&IF(BQ46="","",", ")&BQ46&IF(BQ47="","",", ")&BQ47
&IF(BQ48="","",", ")&BQ48

It'll be easy to modify the formula so, that any cell except 1st in
range can be empty - simply replace according ", " string with check
expression. When ANY cell in range can be blank, it isn't so easy
anymore - every check has to include the check for whole previous
string, resulting with 8 cells to check to a huge formula like

=BQ41&IF(OR(BQ41="",BQ42=""),"",", ")&BQ42
&IF(OR(BQ41&BQ42="",BQ43=""),"",", ")&BQ43
&IF(OR(BQ41&BQ42&BQ43="",BQ44=""),"",", ")&BQ44
&IF(OR(BQ41&BQ42&BQ43&BQ44="",BQ45=""),"",", ")&BQ45
&IF(OR(BQ41&BQ42&BG43&BQ44&BQ45="",BQ46=""),"",", ")&BQ46
&IF(OR(BQ41&BQ42&BG43&BQ44&BQ45&BQ46="",BQ47=""),"",", ")&BQ47
&IF(OR(BQ41&BQ42&BG43&BQ44&BQ45&BQ46&BQ47="",BQ48=""),"",", ")&BQ48
Click to expand...
....

If spaces in nonblank cells in the original range could be replaced with
nonbreaking spaces, one approach to the general case would be

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&CHAR(127)&A2&CHAR(127)&A3
&CHAR(127)&A4&CHAR(127)&A5&CHAR(127)&A6&CHAR(127)&A7&CHAR(127)&A8,
" ",CHAR(160)),CHAR(127)," "))," ",", ")

For that matter, brute force can be dealt with by adding a comma before each
nonempty entry in the range, then deleting the first one from the result.

=SUBSTITUTE(IF(A1<>"",", "&A1,"")&IF(A2<>"",", "&A2,"")
&IF(A3<>"",", "&A3,"")&IF(A4<>"",", "&A4,"")&IF(A5<>"",", "&A5,"")
&IF(A6<>"",", "&A6,"")&IF(A7<>"",", "&A7,"")&IF(A8<>"",", "&A8,""),
", ","",1)
 
Hi Harlan

Because this last one I saved your reply to my archive. Of-course it isn't
for use in some big tables, but it'll do very nice in lesser ones.
 
Neil,

Here's a UDF that will accomodate a varying count items:

Call it in a cell, thus:
=MakeList(A1:A8)

Paste the following into a module:

Function MakeList(InData As Range) As String
Dim i As Integer
Dim NotFirst As Boolean

For i = 1 To InData.Count
If NotFirst And InData(i) <> "" Then MakeList = MakeList & ", "
MakeList = MakeList & InData(i) ' append entry
If InData(i) <> "" Then NotFirst = True
Next i
End Function
 
Sorry, looks like a fair bit of line breaks mangled the suggested formula in
the post ..

Here's a hopefully less mangled re-post of the same formula:

Try:

=IF(COUNTA(BQ41:BQ48)<>0,CHOOSE(COUNTA(BQ41:BQ48),BQ41,BQ41&",
"&BQ42,BQ41&", "&BQ42&", "&BQ43,BQ41&", "&BQ42&", "&BQ43&", "&BQ44,BQ41&",
"&BQ42&", "&BQ43&", "&BQ44&", "&BQ45,BQ41&", "&BQ42&", "&BQ43&", "&BQ44&",
"&BQ45&", "&BQ46,BQ41&", "&BQ42&", "&BQ43&", "&BQ44&", "&BQ45&", "&BQ46&",
"&BQ47,BQ41&", "&BQ42&", "&BQ43&", "&BQ44&", "&BQ45&", "&BQ46&", "&BQ47&",
"&BQ48),"")

The formula assumes that : > .... filled cells will always be uppermost ...
in the range BQ41:BQ48
 
Back
Top