Using isblank and Concatenate

Hi KC,

Your formula is doing exactly what it's written to do. BC2 is not 'blank'.

When my revised version of your formula returned the same results as yours had, I entered =LEN(BC2) into a convenient cell (BD2). The result was 1, indicating BC2 contains a space (or other invisible character).

If BC2 contains directly entered data, click on the cell,then press delete.

IF BC2 contains a formula collecting data from elsewhere in the table (or in another table), you'll need to edit that formula to return a null string ( "" ) to represent a 'blank' cell. I suspect the it currently returns a single space.

Here's the table I constructed, then pasted your data into the indicated columns.

My revised formula is in the first four rows. Your supplied formula is in the bottom row (referencing data in row 2).

Row 2 appearance of both formulas below.

original

B2: IF(ISBLANK(BC2),CONCATENATE(AY2,","," (",AZ2,"), ",BA2,"%, [",BB2,"]"),CONCATENATE(AY2,","," (",AZ2,"), ",BA2,"%, [",BB2,"] | ",BC2,", (",BD2,"), [",BF2,"]"))

revised

IF(ISBLANK(AY2),"",CONCATENATE(AY2,","," (",AZ2,"), ",BA2,"%, [",BB2,"]"))&IF(LEN(BC2)<1,"",CONCATENATE(" | ",BC2,", (",BD2,"), [",BF2,"]"))

Note that the 50%item is missing in rows 3 and 5 as I did not insert the BD2 reference missing from the original.

Regards,

Barry

Adding some notes on my changes:

IF(ISBLANK(AY2),"",CONCATENATE(AY2,","," (",AZ2,"), ",BA2,"%, [",BB2,"]"))&IF(LEN(BC2)<1,"",CONCATENATE(" | ",BC2,", (",BD2,"), [",BF2,"]"))

Changes the initial test from BC2 to AY2:

IF AY is empty, return a null string, otherwise, process the first four cells, then check for part 2

Test BC. IF BC is empty, return a null string and exit, otherwise, process the next set of cells.

The concatenation operator is used to connect the results of part 1 and part 2 into a single string. The space-pipe-space string is included in the part 2 result if that part is calculated.

I used a different 'blank' test—LEN(BC2)<1—for no particular reason other than it's the one I've used 'forever'. For ease of reading, pick one or the other and use the same test for both occurrences.

Regards,

Barry

"I like the LEN test - I need to spend more time with that and figure it out."

HI KC,

It's pretty simple, actually.

LEN gets the LENgth (in number of characters) of the data displayed in the target cell, and returns that number.

IF(LEN(A2)<1,iftrue,iffalse)

If there's nothing in A2, LEN returns 0 and LEN(A2)<1 returns TRUE. IF does whatever is in the 'iftrue' space.

If there's anything in A2 (including 0, x, √, or even a single space) LEN returns a number that is 1 or greater, and LEN(A2) returns FALSE. IF does whatever is in the 'iffalse' space.

Used as a switch to prevent calculation until an entry has been made in A2: IF(LEN(A2)<1,"",formula)

Regards,

Barry