Eliminate space in formula result in Excel 4

[johnbarr44]

I am using the formula format =D86&" "&E86 to merge two cells (Street Number and Street Name) into one address cell (Address 1) for a mail merge to create a list of names and addresses in Word from a spreadsheet.

If there is no street number, the result leaves a space where it would have been, which means that when I print these addresses the 'Address 1' line is not aligned with 'Name' above and 'Address 2' line below.

Is there an easy way to modify the formula to delete any spaces for an empty cell.

Thanks
John B

 

[SkipVought]

hi,
[tt]
=IF(TRIM(D86&" ")="","",D86&" ")&E86
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnbarr44]

Thanks heaps Skip,

Saved me a lot of frustration !

cheers
John B

 

[strongm]

Or

=TRIM(D86&" "&E86)

 

[johnbarr44]

Thank you, strongm,
very elegant.
John B