Importing data, and removing blank cells

**pauls** · 03-04-2009, 08:07 AM

We are working on an address label sheet (i.e. sheet of 8 labels with multiple addresses to affix to customer orders).

Data is imported into Excel and then printed.

The label is in the following format,

---------------------------------------
Return Address - 1A The Street, Someplace,
Somewhere, AA1 1AA, United Kingdom.

(CUSTOMER NAME)
(ADDRESS 1)
(ADDRESS 2)
(ADDRESS 3)
(POSTCODE)
(COUNTRY)
----------------------------------------

For the majority of orders this is fine, but some customers do not enter anything in the (ADDRESS 3) row or (POSTCODE) row, leaving these cells blank in Excel, and an address label printed as follows,

---------------------------------------
Return Address - 1A The Street, Someplace,
Somewhere, AA1 1AA, United Kingdom.

MR A SMITH
14 IRISH STREET

DUBLIN

IRELAND
----------------------------------------

This looks rubbish! Is there a way to format the label properly, for example a formula to move cell text to the cell above or cell below if it is empty to give the desired format (below)?

---------------------------------------
Return Address - 1A The Street, Someplace,
Somewhere, AA1 1AA, United Kingdom.

MR A SMITH
14 IRISH STREET
DUBLIN
IRELAND
----------------------------------------

**martindwilson** · 03-04-2009, 08:42 AM

have you tried filtering on non blanks?

**pauls** · 03-04-2009, 07:05 PM

Would that not need to be done manually?

We are trying to find a solution that automates the process, so that when the data is imported it is in the correct format.

**teylyn** · 03-04-2009, 07:28 PM

Hi,

if you used Word mail merge, there's a biult-in functionality to skip blanks, so the problem won't come up.

**pauls** · 03-05-2009, 04:32 AM

I have not got much experience with Word as I love all the features of Excel.

Can data be imported into Word in the same way as Excel, with fixed widths?

Thanks for the information. I will look into Mail Merge and see if I can knock something up.

**teylyn** · 03-05-2009, 03:54 PM

Hi,

Word has ready made templates for address labels, based on different manufacturer's address label sheets.

Just follow the mail merge wizard and go through it step by step. It's really hard to mess up.

You can still have your raw data in Excel, with some lines having blanks in some columns, others may have all fields filled. Just your normal data records.

Word will open Excel as the data source for the addresses. The labels are layed out on the Word document and the addresses are put on the labels. Blanks are skipped and Word will not print blank lines, so each address looks properly formatted.

Check it out. It's a great tool.

cheers