Want Macro to join lines and delete numbers

Benali · #1 04-29-2020, 01:45 PM

Greetings,

I am not certain what to ask as my knowledge of macros is limited to excel. What i am trying to do is take a transcription form youtube video. Copy and paste to a word document and push a button to have it move all the lines together. Below is how the transcript looks. I want to eliminate the numbers that go from 00:00 to 99:99 and join the sentences together if its even possible. It is time consuming to delete , enter, space ect… as you can imagine. We appreciate your help in advance as i am sure there are some gurus on here.

00:04
and then this you probably were it was
00:07
been so hard for us United States
00:09
I think we've solved we've tried to get
00:15
a private decree in the United States
00:16
and then if I told you that I felt
00:18
fifteen people I probably not funny
00:20
every time I you know I helped a guy in
00:26
Florida this foreclosure he was studying
00:34
kept mailing it into the clerk should
00:36
keep returning it no notes we put a
00:40

money order everything we could think of
00:42
she returned the whole thing I like I'm
00:46
sorry what your state is doing it's just

macropod · #2 04-29-2020, 03:15 PM

You could do this with a wildcard Find/Replace, where:
Find = ([0-9])^13
Replace = \1^t

No code required.

Note: the above inserts a TAB after the time. If you'd prefer a space, replace the ^t with a space character.

Benali · #3 04-29-2020, 04:02 PM

macropod,

I honestly have no clue what to do with this. Can you "please" give me some instructions how to make it work

macropod · #4 04-29-2020, 04:07 PM

Are you saying you don't even know how to use Find/Replace???

Benali · #5 04-29-2020, 05:00 PM

that I do, I was hoping for a macro to do it automatically? I guess I do not understand how to apply the formula

Guessed · #6 04-29-2020, 05:07 PM

If using Wildcards are not your thing, you can also do most of this by selecting it all and converting to a 2 column table separated by paragraphs. This will give you a column containing the time stamps and a second column containing the text. Then select the text column, copy it and use Paste Special to paste as Text Only to get just that text in consecutive paragraphs.

The final step would be to use Find and Replace to replace a paragraph mark '^p' with a space.

macropod · #7 04-29-2020, 05:13 PM

Even the macro recorder would give you a useable macro if you plugged the Find/Replace into it. That said, try:

Code:

Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "([0-9])^13"
    .Replacement.Text = "\1^t"
    .Forward = True
    .Format = False
    .Wrap = wdFindContinue
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll
  End With
End With
Application.ScreenUpdating = True
End Sub

For PC macro installation & usage instructions, see: Installing Macros
For Mac macro installation & usage instructions, see: Word:mac - Install a Macro

Guessed · #8 04-29-2020, 07:00 PM

I got the impression that Benali wants to get rid of the time stamps completely and merge the content into a single paragraph. When I copy/pasted from the original post, the lines were separated by a soft return rather than a paragraph mark so I'm going to assume that is what the code needs to work with.

If the above assumptions are right, building on from Paul's code try...

Code:

Sub Demo()
  Application.ScreenUpdating = False
  With ActiveDocument.Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Text = "([0-9:]{5})"
      .Replacement.Text = " "
      .Forward = True
      .Format = False
      .Wrap = wdFindContinue
      .MatchWildcards = True
      .Execute Replace:=wdReplaceAll
      .Text = "^l"   'or ^13
      .Replacement.Text = ""
      .Execute Replace:=wdReplaceAll
    End With
  End With
  Application.ScreenUpdating = True
End Sub

macropod · #9 04-29-2020, 07:14 PM

Quote:

Originally Posted by Guessed

I got the impression that Benali wants to get rid of the time stamps completely and merge the content into a single paragraph.

You may be right there.

Quote:

Originally Posted by Guessed

When I copy/pasted from the original post, the lines were separated by a soft return rather than a paragraph mark

That's liable to be due to the forum software.

Benali · #10 04-29-2020, 08:49 PM

Quote:

Originally Posted by Guessed

I got the impression that Benali wants to get rid of the time stamps completely and merge the content into a single paragraph. When I copy/pasted from the original post, the lines were separated by a soft return rather than a paragraph mark so I'm going to assume that is what the code needs to work with.

If the above assumptions are right, building on from Paul's code try...

Code:

Sub Demo()
  Application.ScreenUpdating = False
  With ActiveDocument.Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Text = "([0-9:]{5})"
      .Replacement.Text = " "
      .Forward = True
      .Format = False
      .Wrap = wdFindContinue
      .MatchWildcards = True
      .Execute Replace:=wdReplaceAll
      .Text = "^l"   'or ^13
      .Replacement.Text = ""
      .Execute Replace:=wdReplaceAll
    End With
  End With
  Application.ScreenUpdating = True
End Sub

when i run this code it takes away the numbers but does not place the line to the one above and make a complete sentence

Its like this

I sing sit home all morning really but

then you stepped up I'm glad but there's

not enough room for both of us this

morning so I'm gonna pass um we got a

and wish to have this:

I sing sit home all morning really but then you stepped up I'm glad but there's not enough room for both of us this morning so I'm gonna pass um we got a

macropod · #11 04-29-2020, 11:02 PM

Try:

Code:

Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[^13^l][0-9]{2}:[0-9]{2}[!A-Za-z]@([A-Za-z])"
    .Replacement.Text = " \1"
    .Forward = True
    .Format = False
    .Wrap = wdFindContinue
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll
  End With
End With
Application.ScreenUpdating = True
End Sub

Benali · #12 04-30-2020, 07:57 AM

Thank you and much love heading your way!!!!!! PERFECT!!!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Delete lines with just a number on them - use macro or find & replace?	enso	Word	2	05-20-2019 02:58 AM
Select and Delete 2 lines after each blank line	Ziad El Hachem	Word VBA	4	03-21-2017 06:55 PM
delete axis lines	jhatter	PowerPoint	2	06-04-2015 07:14 AM
How can I delete spaces & lines in a table cell	mrayncrental	Word VBA	3	10-20-2014 07:09 PM
cannot delete these lines!	userman	Word	4	05-02-2012 12:34 AM