Hello,

Example: The purpose is to use a macro to separate the two
letters of the state from a text string in a cell that
contains the city and the state, like "Boston MA".

Cell A1 Contains "Boston MA"

I want to be able to move ' MA' to cell B1 using a macro

The key strokes I am currently using are as follows:

Select cell A1 "Boston MA"
[F2] Edit

[Shift]+[Left Arrow][LeftArrow][LeftArrow] to select ' MA'
and the space character to the left of 'MA'

[Ctrl-X] to Cut ' MA'

[TAB] to move to cell B1

[Ctrl-V] to Paste ' MA' in cell B1

[Enter] to go to the next row in cell A2

When I create a macro of the above key strokes it takes
the value of the object cell A1 and pastes it into the A2
cell after I hit Enter.

It seems as though the programming is unable to read the
limited cut and paste command. It seems as though
the "Cell" is the smallest object VBA can handle. Is that
correct?

Is there some way to write a command that truncates a
string in a cell by cutting the last three characters of
the string and moving them to a new cell immediately to
the right of the active cell.

Any answer will help.

Thanks

Lowell

One needs a 2 instead of a 3, in order to avoid a leading space in the
output. And this leaves the Active Cell with the state designation still
in it.

If the city names were all one word, then Data|Text to Columns would
suffice. Otherwise some code has to be written to strip out the State
designation and the space preceding it. If noone answers soon, I'll
work on it.

Alan Beban

Ron de Bruin wrote:
Sub test()
ActiveCell.Offset(0, 1).Value = Right(ActiveCell.Value, 3)
End Sub

Try this

cutting the last three characters of

The OP wants 3 Alan



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Alan Beban" wrote in message ...
One needs a 2 instead of a 3, in order to avoid a leading space in the
output. And this leaves the Active Cell with the state designation still
in it.

If the city names were all one word, then Data|Text to Columns would
suffice. Otherwise some code has to be written to strip out the State
designation and the space preceding it. If noone answers soon, I'll
work on it.

Alan Beban

Ron de Bruin wrote:
Sub test()
ActiveCell.Offset(0, 1).Value = Right(ActiveCell.Value, 3)
End Sub

Try this

Cutting the OP say

Then use this

Sub test()
ActiveCell.Offset(0, 1).Value = Right(ActiveCell.Value, 3)
ActiveCell.Value = Left(ActiveCell.Value, Len(ActiveCell.Value) - 3)
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...

cutting the last three characters of

The OP wants 3 Alan



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Alan Beban" wrote in message ...
One needs a 2 instead of a 3, in order to avoid a leading space in the
output. And this leaves the Active Cell with the state designation still
in it.

If the city names were all one word, then Data|Text to Columns would
suffice. Otherwise some code has to be written to strip out the State
designation and the space preceding it. If noone answers soon, I'll
work on it.

Alan Beban

Ron de Bruin wrote:
Sub test()
ActiveCell.Offset(0, 1).Value = Right(ActiveCell.Value, 3)
End Sub

Try this

Umm something like this should work for you and it will
process the range you want it to. You can also add a
prompt box to get the range so you wouldn't have to change
the macro. To add a input box just add this under Sub Test
()
cRange = Application.InputBox(Prompt:= _
"Please select the Range of Rows you would like to check.
Example D2:D235", Type:=2)

and change For Each c In Worksheets(1).Range("A1:A3") to
For Each c In Worksheets(1).Range(cRange)

Hope I didnt confuse ya


Sub Test()
For Each c In Worksheets(1).Range("A1:A3")
cValue = Right(ActiveCell.Value, 3)
cCount = Len(ActiveCell.Value)
ActiveCell.Offset(0, 1).Value = cValue
ActiveCell.Value = Left(ActiveCell.Value, (cCount - 3))
ActiveCell.Offset(1, 0).Activate

Next c

End Sub





-----Original Message-----
Hello,

Example: The purpose is to use a macro to separate the
two
letters of the state from a text string in a cell that
contains the city and the state, like "Boston MA".

Cell A1 Contains "Boston MA"

I want to be able to move ' MA' to cell B1 using a macro

The key strokes I am currently using are as follows:

Select cell A1 "Boston MA"
[F2] Edit

[Shift]+[Left Arrow][LeftArrow][LeftArrow] to select '
MA'
and the space character to the left of 'MA'

[Ctrl-X] to Cut ' MA'

[TAB] to move to cell B1

[Ctrl-V] to Paste ' MA' in cell B1

[Enter] to go to the next row in cell A2

When I create a macro of the above key strokes it takes
the value of the object cell A1 and pastes it into the A2
cell after I hit Enter.

It seems as though the programming is unable to read the
limited cut and paste command. It seems as though
the "Cell" is the smallest object VBA can handle. Is that
correct?

Is there some way to write a command that truncates a
string in a cell by cutting the last three characters of
the string and moving them to a new cell immediately to
the right of the active cell.

Any answer will help.

Thanks

Lowell
.