How do I concatenate cells based on several if conditions?

Hi people
I wanted to post a question but am having difficulty navigating this forum so if I'm in the wrong place please excuse me.

My question is this: I have a spreadsheet containing address lines 1,2,3,4 and want to turn it into one address block with "/n" being the divider for a new line. I know I could do a concatenate for the cells like for instance =concatenate(B2,"/n",C2,"/n",D2,"/n",E2) which would give me all the address lines with the /n divider... But how do I add an If function that only combines when the address line is not blank?

So far I've tried variations of =if(E2<>"",concatenate(D2,/n",E2)... But basically I fall down when trying to do the conditions so i.e. address lines should only concatenate where there is data in the next proceeding column.

I hope this makes sense to you guys. I know it's possible to be done. Please help

Originally Posted by RobertMika

With small help from VBA
http://www.xl-central.com/concatenat...n-a-range.html

Robert thanks for replying that's much appreciated. I don't have any previous experience of using VBA and So find it a bit daunting. Do you know a solution based on an excel formula? I was hoping to eventually be able to understand whatever it is I am writing.

Thanks again

Originally Posted by romperstomper

Do you have circumstances where say line 1 is blank but line 2 is populated, or are they always populated in order? (ie if line 2 is populated line 1 will always be populated)

Hi Rory - yes if you think of it exactly as a postal address, where some addresses may be longer than others. I have 4 address line columns but some may only have two lines, some may have all four, but there will always be a first line of address. I need to get it so that if there is only one line it selects only that line of data without putting the "/n" in since there isnt a next line to follow. I know for certain you can do this using the IF condidiition but I havent been able to crack it.

Would be forever appreciative if you could help!

Please Login or Register  to view this content.

Like that?

Hi Ben, thank you that's almost right but its still adding the unwanted "/n" at the end which will basically create an unnecessary new line at the end. I only need the /n to identify where there is a next line of address following on. If it is the only line then I need it to end there.. (hope that makes sense). Many thanks for that I think that formula you sent is well on the way toward what I want. Unfortunately I havent been able to tweak it successfully to get precisely what I want

Originally Posted by romperstomper

If any of the lines can be blank (you answered my either/or with "Yes" ), you could try:
=MID(IF(B2<>"","/n"&B2,"")&IF(C2<>"","/n"&C2,"")&IF(D2<>"","/n"&D2,"")&IF(E2<>"","/n"&E2,""),3,32767)
I assume you don't want to end up with /n at the end?

Thanks stomper this looks good. Im going to test it in a minute and let you know how it goes. I understand most of what is going on in that formula but could you tell me, what is the MID function (yes I am a real beginner) and also what is the final part of the string i.e. 3,32767

Stomper you're a legend. It works... I'm likely to use this formula again so you've really helped save me alot of time and perplexment.
Very many thanks for your help.
Could you though, explain to me what the last part of the formula is saying?