Removing blank cells in rows of data

[Andy in Edinburgh]

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

 

[Teethless mama]

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

 

[Andy in Edinburgh]

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.

 

[Gord Dibben]

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

 

[Andy in Edinburgh]

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

 

[Andy in Edinburgh]

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.

 

[Gord Dibben]

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

 

[Andy in Edinburgh]

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]

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