Hi

I have a list of addresses some of the addresses have more lines in them
than others see below.

A B C D E
Mr Smith 5 example St. Hassocks W Sussex BN3 7HJ
Mr Jones 22 Hope Road London SW19 1QW

The list is approximately 700 rows long.

Is there a formula that would move up the lines of the address so they are
all in adjacent cells e.g.
A B C D E
Mr Jones 22 Hope Road London SW19 1QW

Thanks

Andy

Select range then hit F5 Special Blanks OK out Edit Delete Shift
cells left OK out


"Andy in Edinburgh" wrote:

Hi

I have a list of addresses some of the addresses have more lines in them
than others see below.

A B C D E
Mr Smith 5 example St. Hassocks W Sussex BN3 7HJ
Mr Jones 22 Hope Road London SW19 1QW

The list is approximately 700 rows long.

Is there a formula that would move up the lines of the address so they are
all in adjacent cells e.g.
A B C D E
Mr Jones 22 Hope Road London SW19 1QW

Thanks

Andy

I selected the range of cells hit F5 the menu appeared I clicked special,
blanks, ok. However it then displayed an error message no cells found.

"Teethless mama" wrote:

Select range then hit F5 Special Blanks OK out Edit Delete Shift
cells left OK out


"Andy in Edinburgh" wrote:

Hi

I have a list of addresses some of the addresses have more lines in them
than others see below.

A B C D E
Mr Smith 5 example St. Hassocks W Sussex BN3 7HJ
Mr Jones 22 Hope Road London SW19 1QW

The list is approximately 700 rows long.

Is there a formula that would move up the lines of the address so they are
all in adjacent cells e.g.
A B C D E
Mr Jones 22 Hope Road London SW19 1QW

Thanks

Andy

Are the blank cells truly blank or blank due to formulas returning ""?

If not the above, are there any spaces in the blank cells?

Test a few of them with =LEN(cellref)


Gord Dibben MS Excel MVP

On Thu, 4 Sep 2008 07:43:01 -0700, Andy in Edinburgh
wrote:

I selected the range of cells hit F5 the menu appeared I clicked special,
blanks, ok. However it then displayed an error message no cells found.

"Teethless mama" wrote:

Select range then hit F5 Special Blanks OK out Edit Delete Shift
cells left OK out


"Andy in Edinburgh" wrote:

Hi

I have a list of addresses some of the addresses have more lines in them
than others see below.

A B C D E
Mr Smith 5 example St. Hassocks W Sussex BN3 7HJ
Mr Jones 22 Hope Road London SW19 1QW

The list is approximately 700 rows long.

Is there a formula that would move up the lines of the address so they are
all in adjacent cells e.g.
A B C D E
Mr Jones 22 Hope Road London SW19 1QW

Thanks

Andy

A zero appears so i'm guessing that means the cell is empty.

"Gord Dibben" wrote:

Are the blank cells truly blank or blank due to formulas returning ""?

If not the above, are there any spaces in the blank cells?

Test a few of them with =LEN(cellref)


Gord Dibben MS Excel MVP

On Thu, 4 Sep 2008 07:43:01 -0700, Andy in Edinburgh
wrote:

I selected the range of cells hit F5 the menu appeared I clicked special,
blanks, ok. However it then displayed an error message no cells found.

"Teethless mama" wrote:

Select range then hit F5 Special Blanks OK out Edit Delete Shift
cells left OK out


"Andy in Edinburgh" wrote:

Hi

I have a list of addresses some of the addresses have more lines in them
than others see below.

A B C D E
Mr Smith 5 example St. Hassocks W Sussex BN3 7HJ
Mr Jones 22 Hope Road London SW19 1QW

The list is approximately 700 rows long.

Is there a formula that would move up the lines of the address so they are
all in adjacent cells e.g.
A B C D E
Mr Jones 22 Hope Road London SW19 1QW

Thanks

Andy

I'm guessing there must be some hidden formatting as if I click on the blank
cells and press delete using the F5 function selects those blank cells that
i've clicked on.

"Andy in Edinburgh" wrote:

A zero appears so i'm guessing that means the cell is empty.

"Gord Dibben" wrote:

Are the blank cells truly blank or blank due to formulas returning ""?

If not the above, are there any spaces in the blank cells?

Test a few of them with =LEN(cellref)


Gord Dibben MS Excel MVP

On Thu, 4 Sep 2008 07:43:01 -0700, Andy in Edinburgh
wrote:

I selected the range of cells hit F5 the menu appeared I clicked special,
blanks, ok. However it then displayed an error message no cells found.

"Teethless mama" wrote:

Select range then hit F5 Special Blanks OK out Edit Delete Shift
cells left OK out


"Andy in Edinburgh" wrote:

Hi

I have a list of addresses some of the addresses have more lines in them
than others see below.

A B C D E
Mr Smith 5 example St. Hassocks W Sussex BN3 7HJ
Mr Jones 22 Hope Road London SW19 1QW

The list is approximately 700 rows long.

Is there a formula that would move up the lines of the address so they are
all in adjacent cells e.g.
A B C D E
Mr Jones 22 Hope Road London SW19 1QW

Thanks

Andy