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

I'm guessing what you want to do is modify the existing data so that the Mrs
Jones' postcode moves from col E to col D. I'm also assuming the headers are
in row 1 and the data starts in row 2.

In your example, this finds the blank cell at D3, copies E3 to D3 then
deletes E3.

Sub test()
For r = 2 To 3 ' change 3 to match the last line of data in your file
For c = 2 To 4
If Cells(r, c).Value = "" Then
Cells(r, c).Value = Cells(r, c + 1).Value
Cells(r, c + 1).Value = ""
End If
Next c
Next r
End Sub



--
Ian
--
"Andy in Edinburgh" wrote in
message ...
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

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

Hi Duke

The problem with this is that the account numbers will shift to the left.
The OP wanted the account numbers to be undisturbed.

Andy would need to copy the address data to another sheet and apply your fix
there before copying the revised data back.

--
Ian
--
"Duke Carey" wrote in message
...
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