Help creating and Find & Replace macro

[Nancy T.]

This situation happens to me so often, I really want to create a macro to get
the job done. I'm just not sure how to do this.

I get Word documents that have spaces instead of tabs to align text into
columns. (Yes, it is very annoying.) Right now, I have to find the longest
one (the one with the most spaces), copy those spaces, and paste them into
the Find & Replace dialog box under find what. I enter ^t in the Replace box
and do a Replace All. I then delete one space and hit Replace All again. If
there are anywhere from 5 to 50 spaces, you can see that this would take
quite a long time. Is there anyway to create a loop that would start with the
number of spaces I specify and remove one space with each loop through? My
brain is about to explode trying to figure this one out.

 

[Klaus Linke]

Hi Nancy,

You can replace any amount of white space in one replacement:
Find what: ^w
Replace with: ^t
(you can find both white space and the tab under "Special" in the dialog, so
you don't have to remember)

But maybe it would be simpler to use the "table drawing" tool (the first
button on the "Tables and Borders" toolbar).
I've described how you can use it here:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Board=wrd&Number=176778
It'll remove the spaces for you, and could keep everything properly aligned.
A table is better than using tabs in the long run anyway...

Regards,
Klaus

 

[Nancy T.]

The White Space option doesn't work because then it also puts tabs between
words that belong in the same column.

The problem with the Drawing Table option is that there are often 50 or more
rows or text. Drawing a table that large would take longer than what I do
now. Also, it wouldn't let me draw the table into the next page.

Thanks for trying, though.
Nancy

 

[CS Hayes]

I've always been annoyed by people who use spaces inordinately. (Yes,
Inordinately, it should be outlawed.)
but, 50 spaces. ?
are these reports of some sort?

I had realized a similar problem with copying pages from screens of older
Databases. All the text was spaced by spaces. Similar to such:

First Last Address Phone
Jim Jones 123 Main 615-555-1212
Mary Doe 615-555-1313
Banks 615-555-1414

and such, they were from print screens of a db

the first thing that I would do to make sense of it was to:
1) set the text to a fixed character width font like "Courier"
2) Turn the "view hidden characters" option on

This let me at least see what I was up against

 

[CS Hayes]

after this step you can parse the test through excel:

here's a neat article on the subject:
http://www.utoronto.ca/ams/news/91/docs/91-10.htm

hope it helps

 

[Helmut Weber]

Hi Nancy,

you could replace two or more spaces with tabs, like this:

Sub Test4001()
Dim rDcm As Range
Set rDcm = ActiveDocument.Range
With rDcm.Find
.Text = "[ ]{2,}" ' English and some other languages
' .Text = "[ ]{2;}" ' German and some other languages
.Replacement.Text = "^9"
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
End Sub

However, ask again, if you need to exclude two spaces
after a period ". " or after other punctuation marks
as this is common in US-English at least, afaik.

You may use selection instead of the document's range,
if you want to restrict the action to a part of the doc.


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Klaus Linke]

Sorry, there should have been a space before ^w in "Find what", so that you
match only more than one space.

Klaus

 

[Nancy T.]

Here is the macro I finally came up with:

Sub SpacesToTabs()
Dim strText(40) As String, intCount As Integer
Dim intMaxSpaces As Integer, intLowSpaces As Integer

intMaxSpaces = InputBox("How many spaces do you want to start with?")
intLowSpaces = InputBox("What is the low number of spaces to go down
to?")

For intCount = 1 To intMaxSpaces
strText(intCount) = strText(intCount - 1) & Chr(32)
Next intCount

For intCount = intMaxSpaces To intLowSpaces Step -1
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = strText(intCount)
.Replacement.Text = "^t"
.Forward = True
.Wrap = wdFindStop
End With
Selection.Find.Execute Replace:=wdReplaceAll
Next intCount
End Sub

Thanks for everyone's suggestions. I guess I just had to walk away from the
problem for a while. This works perfectly.

Nancy

 

[Bear]

Nancy:

You could also just ask for the lower limit and let your macro convert
everything with that many or more spaces to a tab.

Sub SpacesToTabs()

Dim objRange As Range
Dim intLowSpaces As Integer

' Set the working range from the insertion point
' to the document end

Set objRange = ActiveDocument.Range
objRange.Start = Selection.Start

' Get the lower limit
intLowSpaces = InputBox(Prompt:="Enter the minimum number of spaces.", _
Title:="Tab-U-Lizer")

' Find the first instance
With objRange.Find
.Text = String(intLowSpaces, " ")
.Forward = True
.Execute
' Expand to include all spaces, then replace and
' find the next
Do While .Found = True
Do While objRange.Characters.Last.Next = " "
objRange.End = objRange.End + 1
Loop
objRange.Text = vbTab
objRange.End = ActiveDocument.Range.End
.Execute
Loop
End With

MsgBox Prompt:="Word has reached the end of the document.", _
Title:="Tab-U-Lizer"

End Sub

Bear

 

[Nancy T.]

Thanks, that works perfectly too. I don't have to count the spaces with this
one.

Nancy