Cut and paste part of a string in VBA

  • Thread starter Thread starter noah
  • Start date Start date
N

noah

I need to take the right 2 characters of a string and cut and paste the
to another column.
For example: I have "ABC1" in coulmn J and I need to move the "C1" int
column L, leaving only "AB" in column J.
And I'm repeating this for every row. So I'm using VBA code to d
this.
How do I write it in VBA.
Thanks
 
Try this one

Sub test()
Dim cell As Range
For Each cell In Columns("J").Cells.SpecialCells(xlCellTypeConstants)
cell.Offset(0, 2).Value = Right(cell.Value, Len(cell.Value) - 2)
Next
End Sub
 
Use this line

cell.Offset(0, 2).Value = Right(cell.Value, 2)


--
Regards Ron de Bruin
http://www.rondebruin.nl


Ron de Bruin said:
Try this one

Sub test()
Dim cell As Range
For Each cell In Columns("J").Cells.SpecialCells(xlCellTypeConstants)
cell.Offset(0, 2).Value = Right(cell.Value, Len(cell.Value) - 2)
Next
End Sub
Click to expand...
 
I have another code that does that same thing. But what I can't get i
to do; is delete the second 2 from column "J". In other-words, I nee
column "J" to have only the far left remaining.
If column "J" has "ABC1" in a string, final result should be: Colum
"J" = "AB" and column "L" = "C1".
AND sometimes column "J" will have a 5 character string like "ABCD1"
And then I still want column "L" to only have "D1" and column "J"
"ABC" in final result.
I probably need to add another command to it......
What do you think
 
Try this

Sub test()
Dim cell As Range
For Each cell In Columns("J").Cells.SpecialCells(xlCellTypeConstants)
cell.Offset(0, 2).Value = Right(cell.Value, 2)
cell.Value = Left(cell.Value, Len(cell.Value) - 2)
Next
End Sub
 
Back
Top