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

%3CLINGO-SUB%20id%3D%22lingo-sub-2464836%22%20slang%3D%22en-US%22%3ENeed%20help%20to%20cut%20out%20repetitive%20formating%20in%20Word%20to%20then%20copy%20and%20paste%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464836%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%20everyone.%20I%20am%20new%20to%20this%20forum%20and%20hope%20I%20have%20put%20this%20message%20in%20the%20right%20place.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20do%20a%20lot%20of%20repetitive%20work%20formatting%20dialogue%20scripts%20that%20I%20usually%20receive%20as%20a%20Word%20document%2C%20and%20I%20then%20have%20to%20reformat%20and%20cut%20and%20paste%20into%20standardised%20Excel%20document%20that%20gives%20each%20person%E2%80%99s%20dialogue%20a%20separate%20cell.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThere%20are%20two%20main%20things%20that%20I%20need%20to%20do%20to%20clean%20up%20the%20initial%20document%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3ERemove%20timecode%20information%20(hh%3Amm%3Ass%3Bff)%2C%20I%20would%20usually%20have%20between%202%20and%203%2C000%20of%20these%20scattered%20throughout%20the%20document.%20%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EJoin%20split%20sentences%20together%20so%20that%20I%20can%20place%20a%20complete%20sentence%20into%20one%20cell.%20%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20current%20workflow%20consists%20of%20going%20line%20by%20line%20to%20first%20delete%20each%20timecode%20and%20then%20I%20deleted%20the%20spaces%20between%20words%20and%20commas%20and%20carriage%20return%20at%20full%20stops%20to%20block%20each%20sentence%20into%20a%20separate%20line.%20I%20then%20select%20the%20entire%20document%20and%20paste%20this%20into%20Excel.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20tried%20the%20following%20in%20Word%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFind%20%26amp%3B%20Replace%20using%20*%20but%20this%20identifies%20all%20the%20numbers%20in%20the%20document%20and%20does%20not%20really%20work%20as%20there%20are%20other%20numbers%20that%20I%20need%20to%20keep.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20used%20%5Ep%20to%20block%20the%20entire%20document%20into%20one%20chunk%20and%20then%20I%20go%20through%20it%20to%20create%20full%20sentences%20in%20blocks.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20then%20cut%20and%20paste%20this%20into%20Excel.%20Remove%20the%20timecode%20using%20Find%20%26amp%3B%20Replace%20%3F%3F%3A%3F%3F%3A%3F%3F%3B%3F%3F%20which%20works%20great%20but%20unfortunately%20does%20not%20work%20in%20Word.%20Then%20I%20remove%20all%20the%20empty%20lines%20(blanks)%20using%20edit%2Fgo%20to.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIt%20is%20a%20very%20long-winded%20process%2C%20and%20I%20was%20wondering%20if%20there%20is%20anyone%20that%20could%20help%20me%20find%20a%20quicker%20way%20of%20doing%20all%20this.%26nbsp%3B%20By%20the%20way%2C%20I%20working%20with%20the%20latest%20versions%20of%20Microsoft%20Office%20365%20on%20a%20mac.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%2C%20and%20looking%20forward%20to%20hearing%20back%20from%20someone.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2465190%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20cut%20out%20repetitive%20formating%20in%20Word%20to%20then%20copy%20and%20paste%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2465190%22%20slang%3D%22en-US%22%3ECan%20anyone%20help%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2465759%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20cut%20out%20repetitive%20formating%20in%20Word%20to%20then%20copy%20and%20paste%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2465759%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1083008%22%20target%3D%22_blank%22%3E%40rom915%3C%2FA%3E%2C%3C%2FP%3E%3CP%3Ethis%20is%20quite%20a%20process.%3C%2FP%3E%3CP%3EI%20think%20you%20might%20benefit%20from%20recording%20the%20repetetive%20actions.%20Record%20the%20repetetive%20action%20as%20a%20macro%2C%20asign%20a%20key%20shortcut%20and%20run%20when%20you%20need%20it%20without%20the%20bother%20of%20always%20going%20through%20the%20set-up%20(delete%20spaces%20between%20words%2C%20commas%20with%20paragraph%20marks%20etc.)%3C%2FP%3E%3CP%3EOn%20the%20concrete%20note%3A%3C%2FP%3E%3CP%3E-%20To%20delete%20the%20time%20stemp%2C%20use%20wildcards.%20Too%20long%20to%20explain%2C%20so%20I%20will%20refer%20you%20to%20an%20article%20that%20helped%20me%20a%20lot%20when%20I%20did%20something%20similar%20to%20you%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwordmvp.com%2FFAQs%2FGeneral%2FUsingWildcards.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EFinding%20and%20replacing%20characters%20using%20wildcards%20(wordmvp.com)%3C%2FA%3E.%20This%20is%20an%20article%20that%20describes%20how%20to%20use%20wildcard%20in%20Word%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.howtogeek.com%2F362551%2Fhow-to-use-wildcards-when-searching-in-word-2016%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EHow%20to%20Use%20Wildcards%20When%20Searching%20in%20Word%202016%20(howtogeek.com)%3C%2FA%3E.%20In%20the%20time%20stamp%20you%20need%20to%20work%20with%20the%20semicolon%20too%20to%20make%20a%20difference%20between%20any%20number%20and%20the%20timestamps.%3C%2FP%3E%3CP%3E-%20To%20join%20truncated%20sentences%2C%20I%20found%20it%20best%20to%20do%20it%20in%20blocks%2C%20running%20a%20macro%20that%20replaces%20the%20extra%20paragraph%20marks.%20Finding%20%5Ep%20replacing%20nothing%2C%20or%20if%20it%20is%20a%20line%20break%2C%20finding%5El.%20You%20might%20be%20happy%20with%20your%20Replace%20all%2C%20though.%3C%2FP%3E%3CP%3E-%20If%20you%20truly%20want%20each%20sentence%20on%20a%20new%20line%2C%20this%20is%20easy%3A%20Find%20%22.%20%22%20(fullstop%20and%20a%20space)%2C%20Replace%20%22.%5Ep%22%20(fullstop%20and%20a%20paragraph%20mark).%20To%20delete%20an%20empty%20line%2C%20also%20easy%3A%20Find%20%22%5Ep%5Ep%22%2C%20Replace%20%22%5Ep%22.%20Finds%20two%20paragraph%20marks%2C%20replaces%20with%20one%2C%20ergo%20deleting%20the%20empty%20paragraph.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20seem%20to%20know%20what%20you%20are%20doing%2C%20you%20just%20need%20some%20finess.%20I%20did%20a%20similar%20work%20to%20yours%2C%20and%20recording%20these%20steps%20helped%20me%20save%20a%20huge%20amount%20of%20mental%20energy.%20And%20time%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%20Lenka%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

5 Replies

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

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!
Hi Lenka, just a quick update. Yes, managed to get everything to work as you suggested. Again, thank you very much for your help.
Cheers!

Hello @rom915 ,

always glad to hear when a suggestion works, especially if we can learn something new alongside it. I was over the moon when I found that article myself!

Take care, be safe. L.