Copied directly from the data
TYREE STEPHEN M 7232 QUEENS RD, KNOXVILLE, TN 37931-2530
U T HEALTH SYSTEM 9000 BOSSS PK DR, BLDG C SUITE 200,
KNOXVILLE, TN 37923
U T HOSPITAL 8229 GOODMAN LN, KNOXVILLE, TN 37920-9548
U T MED CTR P O BOX 9800, KNOXVILLE, TN 37940
UAB KNOXVILLE PO BOX 1234, KNOXVILLE, TN 37901
UHL DAVE N 8217 OAK HILL LN, KNOXVILLE, TN 38819-8718
ULTIMATE AQUARIUM 10911 JOHNSON WOODS DR, KNOXVILLE, TN
UNDERSTAG DANNY R 799 DALEWOOD LN, KNOXVILLE, TN 37923
UNDERGROUND THE 284 W JOHNSON AVE, ACCOUNTS PAYABLE,
KNOXVILLE, TN 37542
UNDERWOOD STAN 2611 LINCOLN AVE, KNOXVILLE, TN 36934-5230
UNDERWOOD JANE D 6135 TOPICS PIKE, KNOXVILLE, TN
UNDERWOOD N 2611 LINCOLN AVE, KNOXVILLE, TN 36934-5230
Did that help?
When the ADDRESS fills two cells, for instance B3 and B4, A4 is empty.
I have written a formula to do this in a new Column (C), then simply copy the entire column to replace Column B with VALUES only, then I will still go back and erase the empty rows. I'll still need a macro for that function.
Here's the formula:
Hope this helps explain. This was a 5803 page PDF that I converted to 7 works of about 900 pages (worksheets) each that I then consolidated to one workbook with 7 worksheets of 64,000 lines each. The data conversion was not perfect from PDF to XLS and I've got a lot of cleanup before the data becomes usable. This is the major issue to help get it more organized.