I need a formula to concatenate cells but skip the blanks and after the blanks put in a carriage return. But I need 3 carriage returns after the first cell. Here is more detail on what I have been doing. Don't laugh.![]()
I have tried to concatenate cells in a row and can get the formula to skip blanks or I can get it to do a carriage return (<br />) after every cell but I do not know what the formula would be to do both.
Ultimately, this is needed for html in a csv file, which is why I need <br /> to print out but it has to be created in excel first.
For example:
If my cells contain the following:
a2 = Milk and Juice. b2 = Bread, c2= Cheese, d2 = blank, e2 = blank, f2 = Bananas g2 = blank and h2 = Ice-cream
Then my concatenate formula =CONCATENATE(a2,"<br />","<br />","<br />",b2,"<br />",C2,"<br />",d2,"<br />",e1,"<br />",f2,"<br />",g2,"<br />",h2)
results in the following:
Milk and Juice.<br /><br /><br />Bread<br />Cheese<br /><br /><br />Bananas<br /><br />Ice-Cream
Which when I process it through the html ends up looking like this:
Milk and Juice.
Bread
Cheese
Bananas
Ice-cream
This formula:
=SUBSTITUTE(TRIM(CE32&" "&CF32&" "&CG32&" "&CH32&" "&CI32&" "&CJ32&" "&CK32)," "," ")
results in this:
Milk and Juice. Bread Cheese Bananas
I need it to look like this with 3 lines after the first and no blank lines with the remaining non-blank cells. Basically, to print out the <br /> only if it is a non-blank but 3 after the first. The first cell will always have a value in it.
Milk and Juice.
Bread
Cheese
Bananas
Ice-cream
Bookmarks