Find First occurance of a CHAR and then check rows until occurance stops - Then execute code

[asolanki]

Hi Guys,

Im trying to parse some data thats come from a PDF to Excel Conversion of Addresses, as its a OCR dump of data its not come out very user friendly. So im trying to work out how to split the data per "Address Record"
I did post this - http://www.mrexcel.com/forum/excel-...sts-rows-adding-blank-row-seperate-group.html - ( but i think i made it too complicated in one go and need to break this down some what- I think i know a logic that may work to split the data up after a think last night, but cant get my head round how to code it effectively)

Heres my thinking:

Loop through Column A from A2 to last row and find the occurance of ":" in the cell
Then once its found this occurance of ":" - get the code to check the next few rows after this - ":" (usually will be 4 rows max(Tel:, Mobile:, Email : or Website - looking at the data ) and check if ":" still occurs in the next rows

If it DOES - move to next row to check if there still is a ":" in the cell - Keep checking the next rows until ":" does not occur
Then
When it Doesn't occur - (This to me signals that the new "Record has started") - a BLANK row is to be inserted above this Row - to split the data

Then the code continues down the column using the logic above until it hits the last row - splitting the records according to the logic above

Some sample data:

Abdallah & Associates
Barclays Plaza, Lower Ground Floor
Loita Street
P. 0. Box 20458, Nairobi 00200
Tel: 020-2211637, 2251602
Mobile: 0727-971610, 0715-777457
Email:info@abdallahandassociatesdental.com
Website: www.abdallahassociatesdental.com
Alexa Dental Clinic --------------------------------this is where it would add the Blank row
Prof. Nelson Awori Centre, 3rd Floor Wing B,
Suite B 1, Ralph Bunche Road
P.O. Box 2680, Nairobi 00202
Tel: 0702-182135, 0788-458661
E-mail: dentalalexa@gmail.com
Alif Medical Centre Ltd.--------------------------------this is where it would add the Blank row above
Bakaal Plaza, Ground Floor
5th Street Off 2nd Avenue Eastleigh
Opp. Makah Guest House (Samad)
P.O Box 69086, Nairobi
Mobile: 0721-752861, 0722-146585
Astradental Clinic--------------------------------this is where it would add the Blank row above
Mpaka Road, Mpaka Plaza, 1st Floor
Tel: 020-2088043
Mobile: 0703-500819, 0732-561558
E-mail: admin@astradental.co.ke
Website: astradental.co.ke
Baraka Dental Clinic--------------------------------this is where it would add the Blank row above
Afya Co-op House 4th Floor
Tom Mboya Street, Opp Khoja Mosque
Tel: 0720-397330, 0723-774266
Baraka Smile Clinic--------------------------------this is where it would add the Blank row above
Deep Blue House 3rd Floor, Room 67
Kahawa Wendani
P.O. Box 42496, Nairobi 00100
Tel: 0726-405187
Barakat Dental Services-----------------------this is where it would add the Blank row above

Looking at the sample data and the logic i have tried to describe - Do you think im on the right track?

Thanks for any help you could provide here, to get me started and any advice to get this done - its the looping and the consectuive row checking that i need to sort out.
Ash

 

[Peter_SSs]

Try this in a copy of your workbook.

Sub Insert_Rows()
  Dim r As Long, LastRow As Long
  
  Application.ScreenUpdating = False
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
  For r = LastRow To 2 Step -1
    If InStr(1, Cells(r, 1), ":", vbTextCompare) = 0 And InStr(1, Cells(r - 1, 1), ":", vbTextCompare) > 0 Then Rows(r).Insert
  Next r
  Application.ScreenUpdating = True
End Sub

 

[asolanki]

Peter,

Thanks so much for your help - so my logic worked for this data dump - and you translating it into code worked a treat

Thanks so much!!

Now on to the next ,now the data is split I need to parse it into the correct columns( Name, Address 1, Address 2,PO Box County, Tel, Mobile, Website...etc) into a master sheet - if you have any suggestions - i would love your help

 

[Peter_SSs]

Peter,

Thanks so much for your help - so my logic worked for this data dump - and you translating it into code worked a treat

Thanks so much!!

No problem, glad to help.

Now on to the next ,now the data is split I need to parse it into the correct columns( Name, Address 1, Address 2,PO Box County, Tel, Mobile, Website...etc) into a master sheet - if you have any suggestions - i would love your help

This sort of task is always more difficult ..

- Addresses have varying number of lines.
- PO box text is not consistent. Even in your small sample you have (at least) these variations "P. O.", "P.O.", "P.O" and no PO Box at all.
- Email prefix is not consistent "Email:", "E-mail:"

You would need to either get your data consistent or develop a careful set of rules or instructions about what would go where in the master sheet.


By the way, are any of those details real that people may not want published publicly? Spammers regularly trawl forums like this for just that sort of information. Refer #13 of the Forum Rules

 

[asolanki]

Hi Pete

Thanks for your thoughts

Ref the Data - it is already publically available on the internet and throughout Kenya - so not private data at all, but will not for next time to be sensitive of this type of data - Thanks

Ash

 

[Peter_SSs]

Ref the Data - it is already publically available on the internet and throughout Kenya - so not private data at all, ...

OK, thanks.