run vba script in word for removing certain characters

Mattc1978 · #1 05-11-2022, 09:33 AM

Ok,

Here is the challenge, I need to remove all instances of brackets, speech marks and * in a word document.

How do i string together the requests either in the CTRL H find and replace function or in VBA.

The problem arises when the information is being transferred into a word template from MS Forms.

It leaves the brackets etc in when i change the docx into doc.

I have done it as something like this \[*\] but i want to string them together to include "*" for example and few others for one button press.

I dont know how to connect them?

I am a total amateur obviously.

Help

macropod · #2 05-11-2022, 03:41 PM

You can't put those variations all into a single Find expression. You could use a macro, however, to automate the process. For example:

Code:

Sub BulkFindReplace()
Application.ScreenUpdating = False
Dim ArrFnd As Variant, i As Long
'Array of wildcard Find expressions
ArrFnd = Array("[*\]", "[\*“”""""]")
With ActiveDocument.Range.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Format = False
  .Forward = True
  .MatchWildcards = True
  .Replacement.Text = ""
   'Process each item from ArrFnd
  For i = 1 To UBound(ArrFnd)
    .Text = ArrFnd(i)
    .Execute Replace:=wdReplaceAll
  Next
End With
Application.ScreenUpdating = True
End Sub

Mattc1978 · #3 05-12-2022, 05:57 AM

Hi Macropod,

Can you add change font to arial and remove these types of brackets also [ ]

Many Thanks

How would you best recommend to learn Macro?

Thanks

Matt

macropod · #4 05-12-2022, 04:36 PM

What purpose would be served by changing the font of something you're deleting?

Mattc1978 · #5 05-13-2022, 03:41 AM

Hi Paul,

When the template is populated and a document is created, the document is a doc.x, I have to save as a doc. to remove the active fields. The hangover from the active fields is the brackets, speech marks, *, etc. The macro I hope will remove all of these leaving only the inputs text-wise from the eform. When the eform populates with the text it defaults to a font that can't be used for neurodiversity reasons (dyslexia) and corporate continuity. So I have to manually change to Arial using CTRL A then change to Arial. The form is the quality assured and then posted as a PDF on a tablet, for firefighters to be read as a descriptor of a premises hazards, should they attend a fire there.
If you know of a way to bulk remove empty rows and columns that haven't had input from the eform without using Kutools plus, then that would be amazing. The final bit is to remove anywhere with the word info: if there was nothing pulled through from the eform to populate that row and in turn now being blank delete said row. Many Thanks Matt

macropod · #6 05-13-2022, 03:18 PM

This would be much easier to resolve if we had access to the problem document. Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

Mattc1978 · #7 05-14-2022, 02:43 AM

See Attached - offending document

macropod · #8 05-15-2022, 06:43 PM

Neither of your two attachments contains any fields that I can see. What they contain is content controls. Saving the documents in the .doc or .pdf formats will delete the content controls, leaving only the displayed output.

Instead of trying to use a macro to change the font attributes after the event, you should apply Styles with the desired formatting beforehand (i.e. to the template upon which the document is based).

Moreover, if the brackets, etc. were made part of the content control prompt text, you wouldn't have anything to delete - they'd disappear as soon as the user input something there.

Mattc1978 · #9 05-17-2022, 01:34 AM

Hello,

I sent two doc.x by accident. I've sent the doc. version now.

Yes the content controls are now removed which is what i want. Ok ignore the font issue, can we have a macro that does the other things in one hit?

E.g.

Remove speechmarks, * and []
Then bulk remove empty rows and columns that haven't had input from the eform without using Kutools plus, then that would be amazing. The final bit is to remove anywhere with the word info: if there was nothing pulled through from the eform to populate that row and in turn now being blank delete said row.

macropod · #10 05-17-2022, 02:57 PM

Cleaning up the content is as simple as:

Code:

Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument.Range.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Format = False
  .Forward = True
  .Text = "[\[\]\*“”""""]"
  .MatchWildcards = True
  .Replacement.Text = ""
  .Execute Replace:=wdReplaceAll
End With
Application.ScreenUpdating = True
End Sub

A forum search will turn up macros for deleting empty table rows.

Mattc1978 · #11 05-23-2022, 04:51 AM

So that worked brilliantly, well happy, but the codes i have tried for the delete empty cells returns the error 5991, cannot access the individual rows in this collection, because the table vertically merged cells.
Is there a workaround Thanks Matt
What I'd really want is if the cell only contains "info:" then delete the/ remove the cell, this is because no answer has been returned, so therefore we dont want spaces everywhere.

Thanks Again Matt

macropod · #12 05-25-2022, 04:21 PM

It does, if 'Info:' the only thing in the cell. You asked for a macro that works as follows

Quote:

Originally Posted by Mattc1978

What I'd really want is if the cell only contains "info:" then delete the/ remove the cell

I suspect you still have cells containing rather more than that (e.g. 'header' content before 'Info:', spaces, paragraph breaks, etc. after 'Info:'). Try the following macro, which combines both the text cleanup and row deletion - accommodating that 'extra' content:

Code:

Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Format = False
    .Forward = True
    .Wrap = wdFindContinue
    .Text = "[\[\]\*“”""""]"
    .MatchWildcards = True
    .Replacement.Text = ""
    .Execute Replace:=wdReplaceAll
    .Text = "Info[: ^s.^t^13]{1,}"
    .Replacement.Text = ""
    .Wrap = wdFindStop
  End With
  Do While .Find.Execute
    If .Information(wdWithInTable) = True Then
      If .End = .Cells(1).Range.End - 1 Then .Rows.Delete
    End If
    .Collapse wdCollapseEnd
  Loop
End With
Application.ScreenUpdating = True
End Sub

macropod · #13 05-23-2022, 08:39 AM

Try, for example:

Code:

Sub TblDemo()
Application.ScreenUpdating = False
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "Info:"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
  End With
  Do While .Find.Execute
    If .Information(wdWithInTable) = True Then
      If .Text = Trim(Split(.Cells(1).Range.Text, vbCr)(0)) Then .Rows.Delete
    End If
    .Collapse wdCollapseEnd
  Loop
End With
Application.ScreenUpdating = True
End Sub

Mattc1978 · #14 05-24-2022, 03:05 AM

Yeah didn't work...

macropod · #15 05-24-2022, 07:28 AM

Does for me...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Visual basic Word Script To Remove Specific Areas of a Word Doc	mackied	Word VBA	9	07-30-2017 03:10 PM
Removing characters from a cell	tonydoneese	Excel	4	01-12-2016 07:41 AM
Strange Characters appear when selecting SHOW ALL NON PRINTING CHARACTERS	ann Amber	Word	1	08-01-2015 08:06 PM
Junk characters (box-like characters) in Word file	Sashikala	Word	1	04-20-2010 02:03 PM
Removing data between two characters	Voodoo Child	Excel	1	11-26-2009 01:39 PM