Concatenate adds 0

Hi All, hope you're all keeping well. I am TG
My problem
I'm creating a concatenate column in a sheet which gets its info from another sheet. I want to join house no's and street names e.g. in column L 23 & in column M Main street
My formula is
=CONCATENATE(L1," ",M1), However when there is no number in column L, concatenate puts in a "0" and I get "0 main street". If there's no number I just want Main Street.
The space produced by " " is not a problem but if I could loose that also when there's no number it would be great.
I have set all the pages involved to "not show" zero's
I've also tried =IF(L1<=0,CONCATENATE(L1,M1),CONCATENATE(L1," ",M1))

Thanks in advance.

j

Originally Posted by daddylonglegs

Try this

=IF(L1="","",L1&" ")&M1

Thanks daddylonglegs,

Tried your formula but unfortunately with no success.

Hi Andy
I opened another workbook and copied and used paste special/values and was able to get the result I wanted.
Could it be to do with the source being on another page.
e.g. columns L shows "=box_identity!E1" & column M shows "=box_identity!F1" 9my quote marks)

The workbook is huge so I'm not sure what I could send.

Also I'm using excel 2003.

Thank You Andy, problem solved.
Both suggestions work.
However I'll use the concatenate formula only as it's the only change to the original sheet.

thanks again, I'm sure I'll be back