I have an excel file with a contact list in it. The list is setup as
follows:

A1 Name
A2 Street
A3 City, State Zip

I need to transpose this file so that the three lines become one
record, such as:

A1 Name A2 Street A3 City A4 State A5 Zip
B1 Name B2 Street B3 City B4 State B5 Zip

etc etc through approx 600 addresses.

All the addresses are in 3 line format., and in the last line they
have 'USA' at the end of the line. This I want to eliminate
altogether.

I think I can eliminate the USA using ctrl Find for " USA" (no quotes)
and just delete them all. Am I correct?

I think this code will allow me to break out the rest of row 3:
Dim rngLocations As Range
Dim rngCell As Range
Dim intCharPos As Integer
Dim strCell Contents As String
Set rngLocations = Range ("A1:A785")
For each rngCell in rngLocations.Cells
strCellContents = rngCell.Value
intCharPos = InStr(strCellContents, ",")
rngCell.Offset (0,1).Value = Right(strCellContents, intCharPos - 1)

This will read the city and move it to column C, eliminating the
comma. Then I'll repeat this code using " " as the spot to stop the
grab for the state, and then again for the zip and move them by using
(rngCell.Offset (0,2).Value and rngCell.Offset (0,3).Value to move
state and zip into columns D and E.

My question is how do I tell the sub routine to grab the row 2s and
put them in col B, and then go back and grab the line 3s and work on
them. In other words, line 1 stays put, then I need to move out line 2
to col B, then break out line 3 to their respective columns. Maybe I
am thinking this thru all wrong and I should do each set of 3 lines,
then move to the next set of 3 lines instead of doing all line 2s in a
loop and the doing all line 3s in a loop.

Your advice will be much appreciated

Joanne

ctrl Find did the job on the " USA" part of my problem in case anyone
else has the same question


Joanne wrote:

I have an excel file with a contact list in it. The list is setup as
follows:

A1 Name
A2 Street
A3 City, State Zip

I need to transpose this file so that the three lines become one
record, such as:

A1 Name A2 Street A3 City A4 State A5 Zip
B1 Name B2 Street B3 City B4 State B5 Zip

etc etc through approx 600 addresses.

All the addresses are in 3 line format., and in the last line they
have 'USA' at the end of the line. This I want to eliminate
altogether.

I think I can eliminate the USA using ctrl Find for " USA" (no quotes)
and just delete them all. Am I correct?

I think this code will allow me to break out the rest of row 3:
Dim rngLocations As Range
Dim rngCell As Range
Dim intCharPos As Integer
Dim strCell Contents As String
Set rngLocations = Range ("A1:A785")
For each rngCell in rngLocations.Cells
strCellContents = rngCell.Value
intCharPos = InStr(strCellContents, ",")
rngCell.Offset (0,1).Value = Right(strCellContents, intCharPos - 1)

This will read the city and move it to column C, eliminating the
comma. Then I'll repeat this code using " " as the spot to stop the
grab for the state, and then again for the zip and move them by using
(rngCell.Offset (0,2).Value and rngCell.Offset (0,3).Value to move
state and zip into columns D and E.

My question is how do I tell the sub routine to grab the row 2s and
put them in col B, and then go back and grab the line 3s and work on
them. In other words, line 1 stays put, then I need to move out line 2
to col B, then break out line 3 to their respective columns. Maybe I
am thinking this thru all wrong and I should do each set of 3 lines,
then move to the next set of 3 lines instead of doing all line 2s in a
loop and the doing all line 3s in a loop.

Your advice will be much appreciated

Joanne