Advanced Find & Replace help needed

ballst · #1 12-08-2014, 05:17 AM

Hi, new to forum so advanced apologies if anything in my post is out of place, or unclear.

I have some extemely large dumps of data that I need to sort from their present format into something more user friendly. The three examples below are from 11000+ I'm dealing with.

at_case_id 61626
ca_case_ref LON/00AH/OCE/2014/0295
at_created_dt 2014-12-03 13:26:46
at_audit_type WORDCREATE
at_audit_note Dir/Resp (McMillan Williams Solicitors)
at_user_id 187

at_case_id 61626
ca_case_ref LON/00AH/OCE/2014/0295
at_created_dt 2014-12-03 13:39:56
at_audit_type DOCTOCASE
at_audit_note Directions
at_user_id 187

at_case_id 61636
ca_case_ref LON/00AP/OCE/2014/0296
at_created_dt 2014-11-19 17:30:33
at_audit_type NEWCASE
at_audit_note
at_user_id 49

At it's simplest I need to change the data from a column structure into rows. However its currently too big for Excel, so the idea was to use Word to find each field, and copy and paste into a spreadsheet.

I'm able to do this with fields which are a fixed length using the find all and special characters, so for example all the 11000+ "at_case_id" can be found using the search at_user_id^w^#^#^#^#^#.

The problem I am finding is when the fields are of an unfixed length "at_user_id" for example can be either 2 or 3 characters following the tab / whitespace.

Worse still are "at_audit_type" and "at_audit_note" which as the next example show are again an unfixed length and can carry over to another line.

at_case_id 61626
ca_case_ref LON/00AH/OCE/2014/0295
at_created_dt 2014-11-26 14:40:17
at_audit_type SFLPAPTRK
at_audit_note Send 1st Letters (Paper Track) - Letter(s) sent AND replies expe
cted. Move on to Confirm Paper Track or Change Track
at_user_id 187

at_case_id 61626
ca_case_ref LON/00AH/OCE/2014/0295
at_created_dt 2014-11-26 14:43:44
at_audit_type PAPTRKCRMD
at_audit_note PC (Confirm or Change Track) Result = Standard
at_user_id 187

I'm thinking that a need some kind of find which searches the start of each field, ie the "at_case_id" bit and then the end of the data in that particular line. However I'm damned if I know how...

I'd therefore be really grateful if anyone could point/lead me in the right direction.

Cheers,

Ballst

Robert2 · #2 12-08-2014, 03:58 PM

For the one-line strings, I would use the following wild card searches:
at_case_id [0-9]{1,}
at_user_id [0-9]{1,}
ca_case_ref */*/*/[0-9]{1,}/[0-9]{1,}
at_created_dt [0-9]{4}\-[0-9]{2}\-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}

For the one-line "at_audit" strings, I would use the following:
at_audit_type [A-Z]{1,}
at_audit_note [!^13]{1,}

For the "at_audit" strings that carry over to the next line, it would help to know which character ends the first line. Is it a paragraph end mark (a "pilcrow"), or a line break (Shift+Enter)?

macropod · #3 12-08-2014, 05:24 PM

Try the following Word macro:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim StrFnd As String, i As Long
StrFnd = "at_case_id,ca_case_ref,at_created_dt,at_audit_type,at_audit_note,at_user_id"
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Format = False
    .Forward = True
    .Wrap = wdFindContinue
    .MatchWildcards = True
    .Text = "^13([a-z]{2}[!_])"
    .Replacement.Text = "\1"
    .Execute Replace:=wdReplaceAll
    .Text = "^13{2,}"
    .Replacement.Text = "^p"
    .Execute Replace:=wdReplaceAll
    .Text = Split(StrFnd, ",")(0) & " "
    .Replacement.Text = ""
    .Execute Replace:=wdReplaceAll
    For i = 1 To UBound(Split(StrFnd, ","))
      .Text = "^13" & Split(StrFnd, ",")(i) & " "
      .Replacement.Text = ","
      .Execute Replace:=wdReplaceAll
    Next
  End With
  .InsertBefore StrFnd & vbCr
End With
Application.ScreenUpdating = True
End Sub

If you save the output as a CSV file, it should load directly into Excel. Note that this assumes there are no commas in the data.

For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm.
For Mac macro installation & usage instructions, see: http://word.mvps.org/Mac/InstallMacro.html

ballst · #4 12-09-2014, 04:47 AM

Hi Robert,

Thanks for the reply, I tried this but it didnt work, am I missing something, say inbetween at_case_id [0-9]{1,} ??

For the ones that carry over to another line they end with a para end mark / pilcrow, in fact each line appears to.

Ballst

ballst · #5 12-09-2014, 04:59 AM

Hi Macropod,

Firstly big thanks for your reply, which I've tried but encountered a problem.

Copied your macro into Word and ran it, the result I got is per below, I've only shown one example.

at_case_id,ca_case_ref,at_created_dt,at_audit_type ,at_audit_note,at_user_id
60615
ca_case_ref LON/00AG/OC6/2014/0001
at_created_dt 2014-09-15 09:01:58
at_audit_type NEWCASE
at_audit_note
at_user_id 56

Saved this as a .csv file, however Excel would not open the file, the error message showing that the file format is not valid. Not sure why as I think I followed it correctly.

I've (hopefully) uploaded a small example of the data in its current state, my original post describes how I ideally need it.

Regards,

Ballst

macropod · #6 12-09-2014, 01:23 PM

Please attach a copy of the Word document you're trying to run the macro on. It is impossible to be sure from either your original sample or the content of your Excel workbook how the data appear in your Word document. I had to make some assumptions for the macro that may or may not have been valid.

ballst · #7 12-10-2014, 08:01 AM

Quote:

Originally Posted by macropod

Please attach a copy of the Word document you're trying to run the macro on. It is impossible to be sure from either your original sample or the content of your Excel workbook how the data appear in your Word document. I had to make some assumptions for the macro that may or may not have been valid.

Hi, thanks for the reply.

I've attached a copy of a Word document, this is data straight from the Excel file I posted yesterday. This file is the smallest of 5, but the data in each is presented in the same format.

The data itself was provided to me in Excel, but 3 of the files exceed the 11k+ rows Excel permits, and I recalled that Word would be able to handle all the data, so I've just used the 'open with' feature.

The ultimate aim is for the data to be used in Excel, but spilt into seperate tabs one for each user id.

Hope this helps

Ballst

macropod · #8 12-10-2014, 02:03 PM

Try the following version of the code:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim StrFnd As String, i As Long
StrFnd = "at_case_id,ca_case_ref,at_created_dt,at_audit_type,at_audit_note,at_user_id"
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Format = False
    .Forward = True
    .Wrap = wdFindContinue
    .MatchWildcards = True
    .Text = vbCr
    .Replacement.Text = "^p"
    .Execute Replace:=wdReplaceAll
    .Text = "^13([ ]{15})"
    .Replacement.Text = ""
    .Execute Replace:=wdReplaceAll
    .Text = "^13{2,}"
    .Replacement.Text = "^p"
    .Execute Replace:=wdReplaceAll
    .Text = Split(StrFnd, ",")(0) & "[ ]{1,}"
    .Replacement.Text = ""
    .Execute Replace:=wdReplaceAll
    For i = 1 To UBound(Split(StrFnd, ","))
      .Text = "?" & Split(StrFnd, ",")(i) & "[ ]{1,}"
      .Replacement.Text = ","
      .Execute Replace:=wdReplaceAll
    Next
  End With
  .InsertBefore StrFnd & vbCr
  With .Find
    .Text = "^13{2,}"
    .Replacement.Text = "^p"
    .Execute Replace:=wdReplaceAll
  End With
End With
Application.ScreenUpdating = True
End Sub

ballst · #9 12-11-2014, 07:50 AM

Macropod,

This worked a treat, brilliant

Thanks ever so much, from a cold damp London!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find/Replace Wildcard Needed-Bold & Highlight	rsrasc	Word VBA	3	11-11-2014 03:55 PM
New Find/Replace Wildcard Needed	rsrasc	Word VBA	2	11-11-2014 09:46 AM
Find/Replace Wildcard Needed	rsrasc	Word VBA	4	11-11-2014 08:28 AM
Advanced search and replace for bullet points	TishyMouse	Word	5	02-17-2012 06:32 AM
Advanced find and replace	anno1404	Word	1	10-21-2010 03:58 AM