Moving Conditional data from column to column, repost

[jlbecker67]

How can I move certain data from one column to another? Example:
John Doe.....123.....W...........Main.....St
Jane Doe.....987.....Nowhere.....St

As you can see the street name is shifted left because of the absence of the direction designation. How can I do move all the "St"'s in the wrong column to the right one and then move the street name to the right column? Can I have a formula that finds the two letter designations and moves them to the column to the right? And then have one that finds all data with more than two letters (for the street name) and move it to the column to the right?
Thanks for your help. Sorry I am so ignorant.

Jerry

 

[Damon Ostrander]

Hi Jerry,

This cannot be done with a formula, since a formula cannot insert cells, rows, or columns to a worksheet. It requires a macro (in this case, a fairly simple one). If this is to be completely general, there may be some special cases that you should think about, such as the street naming in Kittredge Colorado where the streets are Avenues name A-Z. So in the street "E Avenue" the E does not stand for East, but is the name of the street. In some areas, the north-east-south-west designator is given last, such as "72nd Ave W". Do you intend to handle this sort of thing?

Anyway, here is a macro that just looks at every cell in column E, and when it finds one empty shifts the cells C and D on that row one cell to the right. Note that it starts with row 2 assuming that there is a header in row 1.

Sub ShiftAddress()
Dim iRow As Long
'start at row 2 because of header in row 1
For iRow = 2 To [a65536].End(xlUp).Row
If IsEmpty(Cells(iRow, 5)) Then
'shift over the cells in columns C and D
Cells(iRow, 3).Insert shift:=xlToRight
End If
Next iRow
End Sub

 

[jlbecker67]

Thanks, Damon. That worked great.

Jerry