concatenation issue with missing values

[AdrianC]

Hi,
Need some help with a concatenation formula.

I have 2 columns: Unit Number (column B) and Street Number (Column C). I need to join these 2 columns but often there is no Unit Number. I've been trying to adjust the formula mentioned in another post but I cant seem to get it to work. I have 29555 rows so dont want to do it manually. I will need a / between the Unit Number and Street number e.g. 7/123

I've tried =IF((B3=""),B3&"/"&C3,B3&"/"&C3), but when the Unit Number is empty it returns the /Street Number e.g. /16

Many thanks,
Adrian

[alansidman]

Try =IF((B3=""),C3,B3&"/"&C3)

Alan

[benishiryo]

hi Adrian, try:
=SUBSTITUTE(IF(B3<>"","/"&B3,"")&IF(C3<>"","/"&C3,""),"/","",1)

[AdrianC]

Try =IF((B3=""),C3,B3&"/"&C3)

Alan

Many thanks for the very simple solution! Knew it would be something simple I'd overlooked.

Cheers,
Adrian

[vlady]

or another way

Please Login or Register  to view this content.