Concatenate excluding blanks

[Chart68]

Hi,

I have a row of data which I need to concatenate with a ; between each value. Unfortunately I have blanks in my data.

Please could you help I have tried to search for an answer to this but cannot find anything

data 4 blank cell 8 25 blank cell blank cell 1 2 blank cell

=CONCATENATE(N2,";",O2,";",P2,";",Q2,";",R2,";",S2,";",T2,";",U2,";",V2)

my output is 4; ;8;25; ; ;1;2; but I dont want the extra ;

Any advice would be much appreciated.

Thanks and Regards

 

[Xiq]

Hi Chart68,
What you can do is (instead of the semicolon) use a space, trim it and then substitute the space for a semicolon. Something like this:

=SUBSTITUTE(TRIM(CONCATENATE(N2," ",O2," ",P2," ",Q2," ",R2," ",S2," ",T2," ",U2," ",V2))," ",";")

 

[Chart68]

Thank you you are a star, you have saved me a lot of time as I was trying all sorts of ways.

Thanks again

 

[Abhijeet R. Joshi]

Hi Chart68,

You have your data from N2 across V2 with some blanks, so here is a simple method.

Cell N3 update the formula"=N2"(without the ")
Cell O3 "=IF(O2<>"",N3&";"&O2,N3)"
select O3 to V3 and hit Ctrl+ R (fillright)

Hope this helps....

 

[Chart68]

Hi Abhi, I tried this but just seem to be getting lots of ; where not needed but dont worry it might be me Im just glad Ive got a solution.

Thanks again

 

[Abhijeet R. Joshi]

Great to hear that you solved it...