Move contents of selected cells to same row into next column adjacent

1.  In column A there is a list of suburb names, state abbreviations, postcodes.
2.  There are also some rows that have only the state abbreviation and postcode.

I want to move the state abbreviation and postcode to the cell in the next column immediately adjacent, on the same row.
Theses are the state abbreviations VIC, TAS, NSW, SA, WA, QLD, ACT

See example of original and required in attached file.
EE-Postcodes-problem-1.xlsx
gregfthompsonAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
I didn't expect empty rows. Try this function:
Sub move_PO()
Dim i As Long, LastRow As Long
Dim j As Integer
Dim ws As Object
Dim PO
PO = Array("VIC", "TAS", "NSW", "SA", "WA", "QLD", "ACT")
Set ws = ActiveSheet
With ws
.UsedRange 'Refresh UsedRange
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

i = 2
  Do While i <= LastRow
    For j = 0 To UBound(PO)
        If InStr(1, .Cells(i, 1), PO(j) & " ", vbBinaryCompare) = 1 Then
            .Cells(i, 3) = .Cells(i, 1)
            .Cells(i, 1) = ""
            Exit For
        End If
    Next j
    i = i + 1
  Loop
End With
End Sub

Open in new window

EE-State-abbreviation-problem-1.xlsm
0
 
als315Commented:
You can use macro like:
Sub move_PO()
Dim i As Long
Dim j As Integer
Dim ws As Object
Dim PO
Set ws = ActiveSheet
PO = Array("VIC", "TAS", "NSW", "SA", "WA", "QLD", "ACT")
i = 2
With ws
  Do Until .Cells(i, 1) = "" And .Cells(i, 3) = ""
    For j = 0 To UBound(PO)
        If InStr(1, .Cells(i, 1), PO(j) & "", vbBinaryCompare) = 1 Then
            .Cells(i, 3) = .Cells(i, 1)
            .Cells(i, 1) = ""
            Exit For
        End If
    Next j
    i = i + 1
  Loop
End With
End Sub

Open in new window

Assign it to some key (in attached sample it is assigned to CTRL+SHIFT+A) and run on your sheet
EE-Postcodes-problem-1.xlsm
0
 
gregfthompsonAuthor Commented:
Thanks.

It worked for some.

But there are still lots that did not move.

A larger list is attached.
EE-State-abbreviation-problem.xlsm
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Gerald ConnollyCommented:
If this is a one off you could just use the text-2-columns function (on the Date Tab) in Excel and then manually edit the exceptions, then use the string join to give you a single cell (ie =c1 & " " & d1), then copy and paste special (Values)
0
 
gregfthompsonAuthor Commented:
Thanks.

That is brilliant.

Can you do a variation that moves the contents of any cell that does not contain a number, into the cell on the right on the same row?
0
 
gregfthompsonAuthor Commented:
Thanks heaps.

I've managed to work it out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.