Deleting 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 past
them
to another column.
For example: I have "ABC1" in coulmn "J" and I need to move the "C1
into column "L", leaving only "AB" in column J.
And I'm repeating this for every row. So I'm using VBA code to do
this.
How do I write it in VBA?

I have another code that takes the right 2 and adds them to column "L"
But what I can't get it to do; is delete the second 2 from column "J"
In other-words, I need column "J" to have only the far left remaining.

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.

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
 
I wasn't sure if you would see my other reply. So I had duped m
question in a new thread.
And your answer works great!
Thanks again
 
Back
Top