select the entire range
press the F5 key, click on Special
Select blanks and click on OK

Now use Edit-Delete-Shift Left

"Andy in Edinburgh" wrote:

Hi

I have a large amount of address data that ultimately I want to concatenate.
However as some addresses have more lines in than others when I concatenate
them I'm left with gaps e.g. between the name of the town and the Post code.

Example data is below

A B C
Name Line 1 Line 2 Line 3 Line 4 Acc No.
Mr Smith 2 High St. Witan Sussex EH1 6JH 1234556
Mrs Jones 1 Jump St. Hove BN1 9OL 2356852

When Concatenated the second line looks like

Mrs Jones
1 Jump St,
Hove

BN1 9OL

I can copy the address data to a different tab before applying the formula
then copy the data back so the account numbers all stay in the same column.

Thanks in advance.

Andy