A column has values similar to this:

Austin, TX 45124
San Antonio, TX 45147

My goal is to move the state to the next column over, and the zip to
the column after state.

I can copy the state and zip, but I don't know how to "move", or cut
and paste them.

Here is where I am starting:

Sub moveAddress()

Dim full As String
Dim state As String
Dim zip As String
Dim pos As Long


For Each Cell In Range("e2:e4")
full = Cell.Text
pos = InStr(full, ",")
state = Mid(full, pos, 4)
Cell.Offset(0, 1).Value = state
Next Cell
End Sub


Again, this copies the state, but doesn't move it. I have found many
posts that move the entire contents of a cell, but obviously I can't do
that.

Thanks

-tom

basically just recorded a macro. see if it works for you

Sub Macro1()
'
With Range("E2:E4")
.TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
End With
With Range("F2:F4")
.TextToColumns Destination:=Range("F2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, _
FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
End With
End Sub

--


Gary


"tom" wrote in message
oups.com...
A column has values similar to this:

Austin, TX 45124
San Antonio, TX 45147

My goal is to move the state to the next column over, and the zip to
the column after state.

I can copy the state and zip, but I don't know how to "move", or cut
and paste them.

Here is where I am starting:

Sub moveAddress()

Dim full As String
Dim state As String
Dim zip As String
Dim pos As Long


For Each Cell In Range("e2:e4")
full = Cell.Text
pos = InStr(full, ",")
state = Mid(full, pos, 4)
Cell.Offset(0, 1).Value = state
Next Cell
End Sub


Again, this copies the state, but doesn't move it. I have found many
posts that move the entire contents of a cell, but obviously I can't do
that.

Thanks

-tom

hey bob, is your clock off?

--


Gary


"Bob Phillips" wrote in message
...
Formula ok?

=LEFT(A2,FIND(,",A2)-1)

and

=RIGHT(A2,LEN(A2)-Find(", ",A2)-1)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tom" wrote in message
oups.com...
A column has values similar to this:

Austin, TX 45124
San Antonio, TX 45147

My goal is to move the state to the next column over, and the zip to
the column after state.

I can copy the state and zip, but I don't know how to "move", or cut
and paste them.

Here is where I am starting:

Sub moveAddress()

Dim full As String
Dim state As String
Dim zip As String
Dim pos As Long


For Each Cell In Range("e2:e4")
full = Cell.Text
pos = InStr(full, ",")
state = Mid(full, pos, 4)
Cell.Offset(0, 1).Value = state
Next Cell
End Sub


Again, this copies the state, but doesn't move it. I have found many
posts that move the entire contents of a cell, but obviously I can't do
that.

Thanks

-tom

Thanks for the help. I was hoping for the VBA, but since it's all
about getting the job done, the formula worked great!

-tom

One more time!

Bob Phillips wrote:

Or maybe now (it changed my clock again LOL).

Bob

"Bob Phillips" wrote in message
...
Hi Gary,

It wasn't the clock, I reset that after a trip to the US, but I forgot to
reset my time zone. Hopefully corrected now.

Thanks

Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
hey bob, is your clock off?

--


Gary


"Bob Phillips" wrote in message
...
Formula ok?

=LEFT(A2,FIND(,",A2)-1)

and

=RIGHT(A2,LEN(A2)-Find(", ",A2)-1)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tom" wrote in message
oups.com...
A column has values similar to this:

Austin, TX 45124
San Antonio, TX 45147

My goal is to move the state to the next column over, and the zip to
the column after state.

I can copy the state and zip, but I don't know how to "move", or cut
and paste them.

Here is where I am starting:

Sub moveAddress()

Dim full As String
Dim state As String
Dim zip As String
Dim pos As Long


For Each Cell In Range("e2:e4")
full = Cell.Text
pos = InStr(full, ",")
state = Mid(full, pos, 4)
Cell.Offset(0, 1).Value = state
Next Cell
End Sub


Again, this copies the state, but doesn't move it. I have found many
posts that move the entire contents of a cell, but obviously I can't
do
that.

Thanks

-tom








--

Dave Peterson

:-(

"Dave Peterson" wrote in message
...
One more time!

Bob Phillips wrote:

Or maybe now (it changed my clock again LOL).

Bob

"Bob Phillips" wrote in message
...
Hi Gary,

It wasn't the clock, I reset that after a trip to the US, but I forgot
to
reset my time zone. Hopefully corrected now.

Thanks

Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
hey bob, is your clock off?

--


Gary


"Bob Phillips" wrote in message
...
Formula ok?

=LEFT(A2,FIND(,",A2)-1)

and

=RIGHT(A2,LEN(A2)-Find(", ",A2)-1)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tom" wrote in message
oups.com...
A column has values similar to this:

Austin, TX 45124
San Antonio, TX 45147

My goal is to move the state to the next column over, and the zip
to
the column after state.

I can copy the state and zip, but I don't know how to "move", or
cut
and paste them.

Here is where I am starting:

Sub moveAddress()

Dim full As String
Dim state As String
Dim zip As String
Dim pos As Long


For Each Cell In Range("e2:e4")
full = Cell.Text
pos = InStr(full, ",")
state = Mid(full, pos, 4)
Cell.Offset(0, 1).Value = state
Next Cell
End Sub


Again, this copies the state, but doesn't move it. I have found
many
posts that move the entire contents of a cell, but obviously I
can't
do
that.

Thanks

-tom








--

Dave Peterson

Formula ok?

=LEFT(A2,FIND(,",A2)-1)

and

=RIGHT(A2,LEN(A2)-Find(", ",A2)-1)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tom" wrote in message
oups.com...
A column has values similar to this:

Austin, TX 45124
San Antonio, TX 45147

My goal is to move the state to the next column over, and the zip to
the column after state.

I can copy the state and zip, but I don't know how to "move", or cut
and paste them.

Here is where I am starting:

Sub moveAddress()

Dim full As String
Dim state As String
Dim zip As String
Dim pos As Long


For Each Cell In Range("e2:e4")
full = Cell.Text
pos = InStr(full, ",")
state = Mid(full, pos, 4)
Cell.Offset(0, 1).Value = state
Next Cell
End Sub


Again, this copies the state, but doesn't move it. I have found many
posts that move the entire contents of a cell, but obviously I can't do
that.

Thanks

-tom

Hi Gary,

It wasn't the clock, I reset that after a trip to the US, but I forgot to
reset my time zone. Hopefully corrected now.

Thanks

Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
hey bob, is your clock off?

--


Gary


"Bob Phillips" wrote in message
...
Formula ok?

=LEFT(A2,FIND(,",A2)-1)

and

=RIGHT(A2,LEN(A2)-Find(", ",A2)-1)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tom" wrote in message
oups.com...
A column has values similar to this:

Austin, TX 45124
San Antonio, TX 45147

My goal is to move the state to the next column over, and the zip to
the column after state.

I can copy the state and zip, but I don't know how to "move", or cut
and paste them.

Here is where I am starting:

Sub moveAddress()

Dim full As String
Dim state As String
Dim zip As String
Dim pos As Long


For Each Cell In Range("e2:e4")
full = Cell.Text
pos = InStr(full, ",")
state = Mid(full, pos, 4)
Cell.Offset(0, 1).Value = state
Next Cell
End Sub


Again, this copies the state, but doesn't move it. I have found many
posts that move the entire contents of a cell, but obviously I can't do
that.

Thanks

-tom

Or maybe now (it changed my clock again LOL).

Bob

"Bob Phillips" wrote in message
...
Hi Gary,

It wasn't the clock, I reset that after a trip to the US, but I forgot to
reset my time zone. Hopefully corrected now.

Thanks

Bob


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
hey bob, is your clock off?

--


Gary


"Bob Phillips" wrote in message
...
Formula ok?

=LEFT(A2,FIND(,",A2)-1)

and

=RIGHT(A2,LEN(A2)-Find(", ",A2)-1)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tom" wrote in message
oups.com...
A column has values similar to this:

Austin, TX 45124
San Antonio, TX 45147

My goal is to move the state to the next column over, and the zip to
the column after state.

I can copy the state and zip, but I don't know how to "move", or cut
and paste them.

Here is where I am starting:

Sub moveAddress()

Dim full As String
Dim state As String
Dim zip As String
Dim pos As Long


For Each Cell In Range("e2:e4")
full = Cell.Text
pos = InStr(full, ",")
state = Mid(full, pos, 4)
Cell.Offset(0, 1).Value = state
Next Cell
End Sub


Again, this copies the state, but doesn't move it. I have found many
posts that move the entire contents of a cell, but obviously I can't
do
that.

Thanks

-tom