-1

I have data in Q column which is returned via another excel formula and I want excel formula to get o/p in R2 cell as shown below

Q column

1234567810 1111 8787

{Blank cell}

1234567811 1111 8787

{Blank cell}

{Blank cell}

{Blank cell}

{Blank cell}

{Blank cell}

{Blank cell}

1234567812 1111 8787

{Blank cell}

{Blank cell}

R2 cell

1234567810 1111 8787,1234567811 1111 8787,1234567812 1111 8787

I tried formula as =TEXTJOIN(",",TRUE,Q2:Q100), but its showing o/p as

1234567810 1111 8787,,1234567811 1111 8787,,,,,,,1234567812 1111 8787,,

So it's not ignoring blank cell values because blank cell values are returned via excel formula which is not actually making them blank but just showing them as blank

K.Dᴀᴠɪs
  • 430
rocky
  • 1

3 Answers3

0

The answers in the comments, and the answers to the other question, look pretty good.  But here’s a simple answer that doesn’t require the use of the TEXTJOIN function (which doesn’t exist in Excel 2013 and below) or the use of VBA.

Set up a “helper column”; for example, Column Z.  In Z2, enter

=IF(Q2<>"",Q2,"")

You might think that =Q2 would be equivalent, but if Q2 is blank, then =Q2 will give you a 0; see Display Blank when Referencing Blank Cell in Excel.  Alternatively, =Q2&"" would probably be just as good.

In Z3, enter

=Z2&IF(AND(Z2<>"",Q3<>""),",","")&Q3

This gives you Q2 and Q3 in the minimized joined (list without blanks) form that you want.  If Z2 (which equals Q2) and Q3 are both non-blank, you get

Q2 & "," & Q3

i.e., apple,banana.  If either Q2 or Q3 is blank, you get

Q2 & Q3

i.e., just plain apple, just plain banana, or blank.

Select Z3 and drag/fill down to Z100.  At each row Zn, this adds Qn to the list so far, inserting a comma only if Qn is not blank.  (Here I have hidden Columns S-Y.)

demo

Then just set R2 to =Z100.  (Or use =Z100&"" if there’s a possibility that the entire list will be blank.)

Scott - Слава Україні
  • 22,532
0

Showing all your information (especially those formulas in column Q) would be helpful. I suspect your formula in Column Q is NOT returning a blank "" but returning a space " " or some other non-printing character.

If it is returning a space, you have two options:

  • Change the Column Q formula to return a blank and use TEXTJOIN as you are doing in your question
  • Use the following formula which will effectively turn the spaces into empties:
=TEXTJOIN(",",TRUE,TRIM(Q2:Q100))

This is an array formula and must be confirmed by holding down ctrl + shift while hitting enter. If you do that properly, Excel will place braces {...} around the formula.

A similar approach could be used for a different non-printing character.

Ron Rosenfeld
  • 9,461
-1

If within the Data Range few blank Cells are available then you need to use the Formula like is below written.

=TEXTJOIN(",",TRUE,Q2,Q4,Q11)
Rajesh Sinha
  • 9,403