2

I have a range of 412 cells (A1: A412). Each cell has a string of data that I OCR'd and exported to Excel. It is a list of names and addresses.

I need VBA code that will loop through each cell, look at the first 5 or 6 characters and determine if the string of text is a Name, Address or City (state&zip) and transpose that data out.

I have code that will transpose the data after every n number of rows, but the data does not get exported this way. Ex: the name might take up two different cells, but I want to merge those two cells in the process.

For example: public information from a state government source

CURRENT LIST (Sheet 1):
Cell A1: A. E. Ewell Investment Co.
Cell A2: P.O. Box 27
Cell A3: Ardmore, OK 73402
Cell A4: Aimie A. Akers, Trustee of the Howard A.
Cell A5: and Aimie A. Akers Revocable Joint Trust
Cell A6: Agreement dated 5/03/1996
Cell A7: 11 Nichols Road
Cell A8: Luray, VA 22835
Cell A9: Alison Evans Taylor
Cell A10: And James Andrew Taylor
Cell A11: 1523 Glenwood Avenue
Cell A12: Oklahoma City, OK 73116

Result: (Sheet 2):
Cell A1: A. E. Ewell Investment Co. Cell B1: P.O. Box 27 Cell C1: Ardmore, OK 73402
Cell A2: Aimie A. Akers, Trustee of the Howard A. and Aimie A. Akers Revocable Joint Trust Agreement dated 5/03/1996 Cell B2: 11 Nichols Road Cell C2:Luray, VA 22835
Cell A3: Alison Evans Taylor and James Andrew Taylor Cell B3: 1523 Glenwood Avenue Cell C3: Oklahoma City, OK 73116

I know this is very specific and there needs to be rules in place, but I just need the base code and I'll implement the rules.

Community
  • 1
  • 1
Clayton McKinney
  • 39
  • 5
  • Also I hope this is mock data... – Marcucciboy2 Sep 07 '18 at 19:40
  • It's public information from a state government source... I do have a program that does standardize the addresses and cities for me – Clayton McKinney Sep 07 '18 at 19:59
  • Are there special indicators in the data for dates/etc? For example how would you suggest differentiating for example `Alexandria Stevens`, a name from `Alexandria Road`, an address – Marcucciboy2 Sep 07 '18 at 20:05
  • Basically I think we need more information about the data’s format or what exactly your program does to pull this together – Marcucciboy2 Sep 07 '18 at 20:07
  • Here’s an example from a simpler set of data https://stackoverflow.com/a/3778357/2727437 – Marcucciboy2 Sep 07 '18 at 20:08
  • I'm using Abbyy Finereader 14 and it pulls the data has a data table so each space from the Name to Address is split into different cells. – Clayton McKinney Sep 07 '18 at 20:19
  • 1
    The data in the PDF doc has it listed as Name *next row* Address * next row * address (it also drops down to the next row if there's any "spillage" from the name or address). For this particular instance and all others, the data on the PDF is in columns. Here's a link to the actual PDF doc; the data I want starts on page 9 (Exhibit A): http://imaging.occeweb.com/AP/CaseFiles/occ30050661.pdf – Clayton McKinney Sep 07 '18 at 20:32
  • 1
    If you can figure out the logic to define the ranges to copy, you can use `Range(destination).PasteSpecial Transpose:=True` to perform the actual transposition. Make the destination cell the next blank cell in column A of sheet 2. It might be easier to get all the cells that will be in the same row and transpose them, then have another operation check if some of the adjacent cells should be merged afterward. Information on PasteSpecial: https://learn.microsoft.com/en-us/office/vba/api/excel.range.pastespecial – DavidP Sep 07 '18 at 20:48
  • GACK. What happened to the sequence numbers that precede the first line of each entity? 1 or 2 or 3 digits followed by a period. That is a sure indicator of the beginning of a new entity. And the row before the next new entity (skip blank lines) is the 3rd line of the address, and prev is 2nd line of address, and all else if 1st line. Exceptions are address unknown at the end of the list. – donPablo Apr 05 '19 at 19:58

0 Answers0