# Office 365 >  > [SOLVED] How do I concatenate cells based on several if conditions?

## Jahzeal

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

----------


## RobertMika

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

----------


## Jahzeal

> 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

----------


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

----------


## Jahzeal

> 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!

----------


## ben_hensel

```
Please Login or Register  to view this content.
```


Like that?

----------


## Jahzeal

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

----------


## romperstomper

If any of the lines can be blank (you answered my either/or with "Yes"  :Wink: ), 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?

----------


## Jahzeal

> 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

----------


## Jahzeal

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.  :Smilie: 
Could you though, explain to me what the last part of the formula is saying?

----------


## romperstomper

The formula prepends any cell text with /n (rather than appending) so if there's any address data the text will always start with "/n". The MID formula simply returns all the text from the 3rd character, thereby skipping the initial /n. (the 32767 is just the largest amount of text you can have in a cell).

----------

