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

Is possible you have formulas in those blank cells that return ""

=LEN(cellref) will give you a zero in that case, but the cells are not
blank.


Gord


On Fri, 5 Sep 2008 02:37:01 -0700, Andy in Edinburgh
wrote:

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

The spreadsheet that i'm trying to do this on was created using a Macro.
Whilst i'm not aware of any formulas on the actual tab i'm woring it would
appear to have placed some invisible formatting over the sheet.

If I were to send you a small sample of the sheet would this help in
resolving the problem?

Andy

"Gord Dibben" wrote:

Is possible you have formulas in those blank cells that return ""

=LEN(cellref) will give you a zero in that case, but the cells are not
blank.


Gord


On Fri, 5 Sep 2008 02:37:01 -0700, Andy in Edinburgh
wrote:

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

Invisible formatting would not prevent a truly blank cell from being
detected.

The macro most likely adds a space or other character into the cells.

Did you try the =LEN(cellref) to see if something is in the cells?

To see what is in the cells, download Chip Pearson's CellView add-in.

http://www.cpearson.com/excel/CellView.aspx

When add-in is loaded..........HexChars is the name...........a new menu
item "View Cell Contents" will be placed in View menu.


Gord

On Sun, 7 Sep 2008 23:55:01 -0700, Andy in Edinburgh
wrote:

The spreadsheet that i'm trying to do this on was created using a Macro.
Whilst i'm not aware of any formulas on the actual tab i'm woring it would
appear to have placed some invisible formatting over the sheet.

If I were to send you a small sample of the sheet would this help in
resolving the problem?

Andy

"Gord Dibben" wrote:

Is possible you have formulas in those blank cells that return ""

=LEN(cellref) will give you a zero in that case, but the cells are not
blank.


Gord


On Fri, 5 Sep 2008 02:37:01 -0700, Andy in Edinburgh
wrote:

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