Forum Discussion

rom915's avatar
rom915
Copper Contributor
Jun 19, 2021

Need help to cut out repetitive formating in Word to then copy and paste into Excel

Hello everyone. I am new to this forum and hope I have put this message in the right place.

 

I do a lot of repetitive work formatting dialogue scripts that I usually receive as a Word document, and I then have to reformat and cut and paste into standardised Excel document that gives each person’s dialogue a separate cell.

 

There are two main things that I need to do to clean up the initial document:

 

  1. Remove timecode information (hh:mm:ss;ff), I would usually have between 2 and 3,000 of these scattered throughout the document.
  2. Join split sentences together so that I can place a complete sentence into one cell.

 

My current workflow consists of going line by line to first delete each timecode and then I deleted the spaces between words and commas and carriage return at full stops to block each sentence into a separate line. I then select the entire document and paste this into Excel.

 

I have tried the following in Word:

 

Find & Replace using * but this identifies all the numbers in the document and does not really work as there are other numbers that I need to keep.

 

I have used ^p to block the entire document into one chunk and then I go through it to create full sentences in blocks.

 

I then cut and paste this into Excel. Remove the timecode using Find & Replace ??:??:??;?? which works great but unfortunately does not work in Word. Then I remove all the empty lines (blanks) using edit/go to.

 

It is a very long-winded process, and I was wondering if there is anyone that could help me find a quicker way of doing all this.  By the way, I working with the latest versions of Microsoft Office 365 on a mac.

 

Thanks, and looking forward to hearing back from someone.

    • Lenka_Kerumova's avatar
      Lenka_Kerumova
      Iron Contributor

      Hello rom915,

      this is quite a process.

      I think you might benefit from recording the repetitive actions. Record the repetitive action as a macro, assign a key shortcut and run when you need it without the bother of always going through the set-up (delete spaces between words, commas with paragraph marks etc.)
      On the concrete note:
      - To delete the time stamp, use wildcards. Too long to explain, so I will refer you to an article that helped me a lot when I did something similar to you: Finding and replacing characters using wildcards (wordmvp.com). This is an article that describes how to use wildcard in Word: How to Use Wildcards When Searching in Word 2016 (howtogeek.com). In the time stamp you need to work with the semicolon too to make a difference between any number and the timestamps.
      - To join truncated sentences, I found it best to do it in blocks, running a macro that replaces the extra paragraph marks. Finding ^p replacing nothing, or if it is a line break, finding ^l. You might be happy with your Replace all, though.
      - If you truly want each sentence on a new line, this is easy: Find ". " (fullstop and a space), Replace ".^p" (full stop and a paragraph mark). To delete an empty line, also easy: Find "^p^p", Replace "^p". Finds two paragraph marks, replaces with one, ergo deleting the empty paragraph.

      You seem to know what you are doing, you just need some finesse. I did a similar work to yours, and recording these steps helped me save a huge amount of mental energy. And time as well.

      Hope this helps. Lenka

      • rom915's avatar
        rom915
        Copper Contributor
        Fantastic and thank you very much for your help Lenka. I will try and get my head around all this during the course of the day as see if I can get all this to work. If I need any further help I will get back to you. Thank you again and have a great day!

Resources