John:
I have done this before.
Step 1
1. Make a backup copy of your files. I always work from a copy in case
I ever need to use the original again.
2. Select the Right Column
3. Cut the column
4. Ctrl+End to go to the bottom of the document
5. Paste the Cut Column so that you have a single column table
6. Do a Search and Replace for the Zip Code. Find What: [^#^#^#^#^#]
Replace with: [BLANK but select Formatting BOLD]
7. Select Table and convert to text with Paragraph Separators
8. This will leave your document with Bold Zip Codes
Step 2
1. Do a Search and Replace for the Zip Code. Find What: [BLANK but
select Formatting BOLD] Replace with: [BLANK but select Formatting BOLD]
2. Note the number of Replacements.
3. Create the following macro:
Sub Labels()
'Macro by Gene
'
Selection.HomeKey Unit:=wdStory
For x = 1 To 10 'Where 10 represents the number you noted
Selection.Find.ClearFormatting
With Selection.Find
.Text = " ^#^#^#^#^#"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
End With
Selection.Find.Execute
Selection.EndKey Unit:=wdLine
Selection.TypeText Text:="~"
Next x
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p"
.Replacement.Text = "^t"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "~^t"
.Replacement.Text = "^p"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p^t"
.Replacement.Text = "^p"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "^p^p"
.Replacement.Text = "^p"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = ", "
.Replacement.Text = "^t"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
End With
Selection.Find.Execute
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
This will leave the addresses mostly tabbed delimted.
Step 3
1. Convert the text to a table
2. Copy into MS Excel
3. Insert a line at the top for your Titles
4. Move the data to the appropriate columns (Use AutoFilter to speed
this up)
Final Step
1. Select the column of data which should be in the format [ST 00000]
2. Split the Column in Excel by Selecting Data. . . . Text to Columns
3. Select Delimited
4. Select Space
5. Finish
Hope this helps.
-Gene
John Earls said:
I am trying to convert 10 individual MS Word 2002 files
(in a 2 column label format) sent to me.
Each mail list numbers over 250 printed pages x 30
records/page = 7500 names per list x 10 lists = 75,000
total records!
Too much to cut and past or re-key in.
These files are mail lists WITH THE FOLLOWING BASIC FORMAT
NAME, TITLE (IF AVAIL)
COMPANY NAME
ADDRESS 1
ADDRESS 2 (IF AVAIL)
CITY, STATE, POSTAL CODE
Depending on the information for each record, sometimes
below each record there is a space or two.
When I convert table [ table-convert-table to text ] to
text successfully, I get the list with varying numbers of
spaces in between each "record". The same is tru when I
save the file as a text only document.
Regardless, my end goal is to import all records into MS
Access or Excel to allow me to further query and sort in
unique ways.
Can anyone help me with specific instruction on this
conversion (to excel or Access)
