Removing blank cells in rows of data

  • Thread starter Thread starter Andy in Edinburgh
  • Start date Start date
A

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
 
Select range then hit F5 > Special > Blanks > OK out > Edit > Delete > Shift
cells left > OK out
 
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.
 
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
 
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.
 
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
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top