How to remove text from concatenated formula if cell is blank?

[mikey42979]

Hi, I have the below forumal that separates text with "|". I would like to know how i can have the formula ignore blank cells so it skips the "|" seperator if the cell is empty.

=INDIRECT("D"&ROW()-11)&" | "&INDIRECT("D"&ROW()-10)&" | "&INDIRECT("D"&ROW()-9)

Sample with current formula: This is text from 11 rows above | This is text from 10 rows above | <-- I need to remove the last "|" separator because nothing is in the cell 9 rows above.

TIA.

[alansidman]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Rick Rothstein]

I think you may be looking for something like this...

Please Login or Register  to view this content.

[mikey42979]

I think you may be looking for something like this...

Please Login or Register  to view this content.

This works great but then i realized I would need a similar formula for a few other lines in the sheet that does almost the same thing. I thought maybe i can replicate your formula for the other lines but im not that good in excel

I have attached the workbook sample.

[Rick Rothstein]

I am completely clear on what you are doing in your sample, but I do have some comments which might help you in constructing your own formula.

First, I do not see why you are using the INDIRECT function and subtracting numbers from the ROW() function. You know the cells where the data is located, so why not use it directly instead of INDIRECT'ing to them? So, instead of this...

INDIRECT("B"&ROW()-11)

from your formula, use this... B10 (and so forth for the other cells). That would make the formula I posted in Message #2 much easier to read...

=REPLACE(IF(B10="",""," | "&B10)&IF(B11="",""," | "&B11)&IF(B12="",""," | "&B12),1,3,"")

Notice the color three colored sections of code... except for the cell reference, they are identical. That is the key to the formula... just concatenate an identical section for each cell you want to examine (changing the cell referenced in that section, of course). Each colored section is looking at the cell to see if it is blank... if it is, that part of the formula output the empty text string ("") meaning nothing is added to the overall output from that section of the formula. However, if the cell is not blank, then your Space/VerticalLine/Space is concatenated onto the front of the cell reference. This happens for each colored section. So either nothing is outputted form that section or the Vertical Bar surrounded by spaces plus the text from the cell referenced in than section is outputted from that section. Now, once all the sections have been concatenated together, only one Vertical Bar will exist between the outputted text; however, there will be a Space/VerticalBar/Space located at the front of text concatenated together from each colored section, so I used the REPLACE function to remove the first three characters thus giving you the clean text output that you wanted.

[mikey42979]

I am completely clear on what you are doing in your sample, but I do have some comments which might help you in constructing your own formula.

First, I do not see why you are using the INDIRECT function and subtracting numbers from the ROW() function. You know the cells where the data is located, so why not use it directly instead of INDIRECT'ing to them? So, instead of this...

INDIRECT("B"&ROW()-11)

from your formula, use this... B10 (and so forth for the other cells). That would make the formula I posted in Message #2 much easier to read...

=REPLACE(IF(B10="",""," | "&B10)&IF(B11="",""," | "&B11)&IF(B12="",""," | "&B12),1,3,"")

Notice the color three colored sections of code... except for the cell reference, they are identical. That is the key to the formula... just concatenate an identical section for each cell you want to examine (changing the cell referenced in that section, of course). Each colored section is looking at the cell to see if it is blank... if it is, that part of the formula output the empty text string ("") meaning nothing is added to the overall output from that section of the formula. However, if the cell is not blank, then your Space/VerticalLine/Space is concatenated onto the front of the cell reference. This happens for each colored section. So either nothing is outputted form that section or the Vertical Bar surrounded by spaces plus the text from the cell referenced in than section is outputted from that section. Now, once all the sections have been concatenated together, only one Vertical Bar will exist between the outputted text; however, there will be a Space/VerticalBar/Space located at the front of text concatenated together from each colored section, so I used the REPLACE function to remove the first three characters thus giving you the clean text output that you wanted.

As i just figured it out on my own i come back to see your other solution lol. Your absolutely right, it does make it easier. My document used direct references in the past but changed to the indirect formula, I don't remember why though. In my original document i have 4 of these stacked on top of each other. I think it may have been so i didnt have to keep updating the cell references but dont recall. Thanks a bunch for your help, i will be converting them to direct references.