Move Part of Cell String

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
news:uC%[email protected]...
> hey bob, is your clock off?
>
> --
>
>
> Gary
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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

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

Bob

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> 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
> news:uC%[email protected]...
> > hey bob, is your clock off?
> >
> > --
> >
> >
> > Gary
> >
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> 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
> > >>
> > >
> > >
> >
> >
>
>

One more time!

Bob Phillips wrote:
>
> Or maybe now (it changed my clock again LOL).
>
> Bob
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> > news:uC%[email protected]...
> > > hey bob, is your clock off?
> > >
> > > --
> > >
> > >
> > > Gary
> > >
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > >> 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" <[email protected]> wrote in message
news:[email protected]...
> One more time!
>
> Bob Phillips wrote:
> >
> > Or maybe now (it changed my clock again LOL).
> >
> > Bob
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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
> > > news:uC%[email protected]...
> > > > hey bob, is your clock off?
> > > >
> > > > --
> > > >
> > > >
> > > > Gary
> > > >
> > > >
> > > > "Bob Phillips" <[email protected]> wrote in message
> > > > news:%[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > >> 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