Formula help...

[omairhe]

Hello Gurus,

The following will output me the "I" character in the sequence of each True Cell in a range and a blank " " similarly.

=aconcat(IF(ISNUMBER(AI7:BB7),"l"," "))

I would like it give a blank " " for cell with formulas giving me 0 numbers also. It is giving "I" for 0 numbers.

will appreciate.

 

[Fluff]

How about

=TEXTJOIN(,,IF((ISNUMBER(AI7:BB7))*(AI7:BB7<>0),"|"," "))

 

[omairhe]

How about

=TEXTJOIN(,,IF((ISNUMBER(AI7:BB7))*(AI7:BB7<>0),""|," "))

Thank you for the input, I tested this but this will join text without alternating between "I" and " "
For instance AI7 = 0 or a blank, Hence first character from the left should be a " " in the cell where the formula goes.
and then if AJ7 = 15 or any value greater than a 0, This should output me "I" as in second character from the left. and so on and so forth.
I could just eliminate the 0 values from the source cells (example, AI7:BB7) by turning the 0s into blanks with IF=0 then Blank. So yes this approach can fix the problem for me as well.

 

[omairhe]

How about

=TEXTJOIN(,,IF((ISNUMBER(AI7:BB7))*(AI7:BB7<>0),"|"," "))

sorry my mistake, I tested again and it works as required..

 

[Fluff]

You're welcome & thanks for the feedback.

 

[omairhe]

Yes it worked.
Thanks for the rescue..