Concatenating text in multiple columns using IF statements and ignoring blank cells

[BlissC]

Hi,

I'm concatenating cells in 4 columns using the following formula:

Please Login or Register  to view this content.

Initially when I tried concatenating the cells, where there was a blank cell in the middle of the cells on some rows, I switched to this formula to stop multiple commas appearing in the middle of the text, and the formula itself works fine.

My problem is that due to some poorly formatted data I inherited, on some rows it's the first cell that's blank, and using this formula when the first cell is blank a comma appears before the text. I've tried adding another IF statement to the front of it to check whether D8 is empty, but then realised that it's already checking to see if D8 is empty.

I did consider wrapping the IF statements in the TRIM function but then realised that wouldn't work because it's extraneous commas I need to trim, not spaces. I can't use TEXTJOIN because although I use Excel 2016 at home, the work PCs are still using Excel 2010. I also considered trying to concatenate a range, but because I'm pulling the data from another worksheet and I'm hiding column G on the original worksheet and not using it in the worksheet where I'm displaying the concatenated text, I can't just specify columns D to H (but the column on the original worksheet can't be deleted because it will be needed once the whole worksheet is updated and it's uploaded back into the system the data's come from.

After looking around online for a solution I’ve tried numerous variations of nested IF statements, many of which I couldn’t get to work. The nearest one I’ve found to doing the job is this:

Please Login or Register  to view this content.

This handles blank cells in any of the columns including where the last column is empty, except for if the first column is blank. I’ve attached a file with some example data on the left, with the results of the formula on the right. The result on row 6 (highlighted in red) is the one that’s displaying incorrectly, with a comma preceding the text from cell F6.

The formula needs to:

1. Check if E4 is blank. If it is blank then display nothing. If it isn’t blank then display the contents of the cell followed by a comma
2. Check if F4 and G4 are blank or have text in them. If they have text then display the text followed by a comma. If they are blank then display nothing
3. Check if I4 is blank or has text in it. If it has text then display the text. If it’s blank then display nothing.
4. If I4 is blank then display G4 without a comma after it

The formula is correctly processing all of these points except the first. I’ve tried the formula below to check whether E4 is blank or contains text:

Please Login or Register  to view this content.

The result of this is shown in the example in cell K13 (highlighted in orange) and simply doesn’t show the contents of E13, but displays a comma. I’m sure I’ve the staring this far too long, but I can’t see what the problem is with the formula. Can anyone see where I’m going wrong at all?

[BlissC]

My apologies. I accidentally double posted this post while trying to edit it. Could a moderator please delete this thread.

Thanks, Bliss

[FDibbins]

I will mark it SOLVED for you