You are here:

Excel/create labels from badly organized spreadsheet


I have been asked to volunteer my time in printing and mailing for a charitable organization  The list provided me is in Excel and the top row contains  names addresses etc but about 200 rows down there is a header as follows:   

Prefix   First Name   Middle Initial   Spouse   Last Name   Address    City   State   Zip Code   Company    
Co. Address Line    Co. City   State   Co.Zip code   

All in all about 14 fields and the problem is that in many cases the company address (where it is a co.) is sometimes under "co.address", and in other cases under "address. Same goes for city, state zip. In other words it's all mixed up. If I were to separate the list leaving the last field as "zip", and do the same thing with the co info to the right, I am left with blank areas in each. The person who created this tells the customer that they printed out the labels from this. I would kike to find out how this was done. Of course I can easily move the field header up to row #1, but how do I handle all the blank fields where printing  is concerned?

I have copied the first 3 rows of this file, changed the real names and saved as txt. The formatting is as per the actual spreadsheet.

As you will be merging the data (I assume) into Word, you actually don't need to worry - as the data is either in one place OR the other, you can simply insert fields for both side by side - the blank data will merge as a blank (and therefore not be present) and the populated data will merge as data.

I'd also suggest leaving the data in the mess it's in and simply doing two merges - one with the top range, one with the bottom - to make it easier, name each range so you can select the named range as the data source.

Hope this makes sense, but if you have any issues do get back to me - I think the subject of merge may have to be a new blog entry on my site !
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 All rights reserved.