Help creating and Find & Replace macro

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
Fri Apr 13 17:38:37 CDT 2007

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." <NancyT@discussions.microsoft.com> schrieb im Newsbeitrag
news:4D3C7017-ABD0-4BBD-B15F-02620678A781@microsoft.com...
> 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.

NancyT
Fri Apr 13 18:54:04 CDT 2007

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

"Klaus Linke" wrote:

> 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." <NancyT@discussions.microsoft.com> schrieb im Newsbeitrag
> news:4D3C7017-ABD0-4BBD-B15F-02620678A781@microsoft.com...
> > 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.
>
>
>

hayes
Fri Apr 13 22:36:01 CDT 2007

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



--
Chris Hayes
Still a beginner (only 12 years)


"Nancy T." wrote:

> 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
>
> "Klaus Linke" wrote:
>
> > 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." <NancyT@discussions.microsoft.com> schrieb im Newsbeitrag
> > news:4D3C7017-ABD0-4BBD-B15F-02620678A781@microsoft.com...
> > > 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.
> >
> >
> >

hayes
Fri Apr 13 22:40:02 CDT 2007

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
--
Chris Hayes
Still a beginner (only 12 years)


"CS Hayes" wrote:

> 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
>
>
>
> --
> Chris Hayes
> Still a beginner (only 12 years)
>
>
> "Nancy T." wrote:
>
> > 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
> >
> > "Klaus Linke" wrote:
> >
> > > 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." <NancyT@discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:4D3C7017-ABD0-4BBD-B15F-02620678A781@microsoft.com...
> > > > 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.
> > >
> > >
> > >

Helmut
Sat Apr 14 02:22:49 CDT 2007

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
Sat Apr 14 06:45:24 CDT 2007

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

Klaus



"Nancy T." <NancyT@discussions.microsoft.com> wrote:
> 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
>
> "Klaus Linke" wrote:
>
>> 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." <NancyT@discussions.microsoft.com> schrieb im Newsbeitrag
>> news:4D3C7017-ABD0-4BBD-B15F-02620678A781@microsoft.com...
>> > 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.
>>
>>
>>

NancyT
Sun Apr 15 01:18:01 CDT 2007

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

"Nancy T." wrote:

> 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.

Bear
Mon Apr 16 11:38:09 CDT 2007

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

NancyT
Mon Apr 16 13:46:17 CDT 2007

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

Nancy

"Bear" wrote:

> 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