Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Using isblank and Concatenate

Help -


I tried to find an answer to this by serching and still can't figure it out. Maybe some one can help me see what I am missing.


I have this set of data:


ay az ba bb bc bd be bf
Pub 1 BMI 100 123456


Pub 1 BMI 50 123456 Pub 2 ASCAP 50 78965


What I need is to concatenate the fields into one that would read like this:


Pub 1, (BMI), 100%, [12345]

Pub 1, (BMI), 50%, [12345] | Pub 2, (ASCAP), 50%, [78965]


SO i need to check if Cell BC is blank and if so, not add anything else, if there is info, I need to add the pipe and the info.


This is my formula:


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


But I still get all the extra brackets and such even if BC is blank.


Thanks for any help.


Cheers

kc

Posted on

Reply
Question marked as Solved
Answer:
Answer:

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

Posted on

Question marked as Helpful

Mar 14, 2018 5:44 AM in response to KPC In response to KPC

"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

There’s more to the conversation

Read all replies

Page content loaded

Mar 13, 2018 1:49 PM in response to KPC In response to KPC

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).

User uploaded file

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

Mar 13, 2018 1:49 PM

Reply Helpful
Question marked as Solved

Mar 13, 2018 2:01 PM in response to Barry In response to 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

Mar 13, 2018 2:01 PM

Reply Helpful
Question marked as Helpful

Mar 14, 2018 5:44 AM in response to KPC In response to KPC

"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

Mar 14, 2018 5:44 AM

Reply Helpful (1)
User profile for user: KPC

Question: Using isblank and Concatenate