Request for help to rearrange data...

[Greg Mouning]

Hi,

I'm working with Word 97 and I wish to rearrange a data source file so that it is consistent. For example, I have the following sample data records:

"John Smith", "1 ABC Lane", "City State Zip"
"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"

As you can see above the Jane Smith record has an additional field "Apt G" field. If I were doing a normal mail merge this would not be a problem. However, I need to export the data for another program rearranging the data using the following format:

"", "John Smith", "1 ABC Lane", "City State Zip"
"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"

Is there a Find/Replace command I can use on this data or must I create a macro to handle this problem?

Thanks in advance for your assistance.

Regards,
Greg

 

[Doug Robbins - Word MVP]

Select all of the records, then run the following macro:

Dim i As Long, j As Long, data As Table, datarange As Range
Selection.ConvertToTable wdSeparateByCommas, , 4
Set data = Selection.Tables(1)
For i = 1 To data.Rows.Count
If Len(data.Cell(i, 4).Range) = 2 Then
For j = 4 To 2 Step -1
Set datarange = data.Cell(i, j - 1).Range
datarange.End = datarange.End - 1
data.Cell(i, j).Range = datarange
Next j
data.Cell(i, 1).Range = Left(data.Cell(i, 1).Range, 1) &
Left(data.Cell(i, 1).Range, 1)
End If
Next i
data.ConvertToText wdSeparateByCommas


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Hi,

I'm working with Word 97 and I wish to rearrange a data source file so

that it is consistent. For example, I have the following sample data
records:

"John Smith", "1 ABC Lane", "City State Zip"
"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"

As you can see above the Jane Smith record has an additional field "Apt G"

field. If I were doing a normal mail merge this would not be a problem.
However, I need to export the data for another program rearranging the data
using the following format:

 

[Helmut Weber]

Hi Greg,
(much nicer to see a real name here)

"", "John Smith", "1 ABC Lane", "City State Zip"
"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"

is that really what you want?
Once the name in the second field of the record
and then again in the first field?
Shouldn't it be like this:

 

[Greg Mouning]

Thanks Doug, this is just what I needed and it works like a charm. However, can you explain why this macro adds an additional blank space after the commas?

-Greg

 

[Greg Mouning]

Greetings Helmut,

I appreciate your comment but yes the macro Doug created is what I wanted.

Regards,
Greg

 

[Doug Robbins - Word MVP]

It's not the macro that does it, its to conversion of the table to text. It
happens even if that step is performed by using that item on the Tables
menu. If it causes a problem, you could use Edit>Replace to get rid of that
space.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Thanks Doug, this is just what I needed and it works like a charm.

However, can you explain why this macro adds an additional blank space after
the commas?

 

[Greg]

Hi,

I have data in the following address label format:

Name
Address Line 1
Address Line 2
Address Line 3
City State Zip
Foreign

Dear Salutation,

Because I'm importing this data into a different program, I need to collapse
all blank lines and re-insert them above the Name. In other words, I have to
re-arrange the data so that it consistently contains 8 lines per record.

Any advice you can give on how to accomplish this is greatly appreciated!

Regards,
Greg