Take the 2-minute tour ×

I need a macro to cut and paste a portion of a string from column A to column B if the string has a - sign in the 9th character. I found some code on stackoverflow that will copy/paste the text portion but does not cut/paste. My data looks like this


Here's the code I have so far:

Public Sub LeftSub()
 Dim cell As Range
 Dim sourceRange As Range

 Set sourceRange = Sheet1.Range("A1:A180")

 For Each cell In sourceRange

     If Mid(cell.Value, 9, 1) = "-" Then

     'this code should cut/paste the value from col A to col B, not copy/paste
     Sheets("Sheet1").Range("B" & cell.Row).Value = Mid(cell.Value, 9, 20)

     End If
 End Sub

Any help is much appreciated.
Thank you.

share|improve this question
If your data is as you have shown in the sample above then you don't need VBA for this. You can use Text To Columns as well :) –  Siddharth Rout Feb 17 '13 at 6:34

1 Answer 1

If you want the value you are pasting into column B removed from column A, use this:

    cell.Offset(, 1).Value = Mid(cell.Value, 9)
    cell.Value = Left(cell, 8)
End If

As Sid and Brett point out, we don't need the number of characters argument for the Mid function if we are getting the rest of the value from the midpoint on. If you want the dash at the start of your Column B value, set the midpoint to 9. If you want to omit it, set it to 10.

If you want to cut/paste the entire value from column A to B, use this:

    'this code WILL cut/paste the value from col A to col B, not copy/paste
    cell.Cut cell.Offset(, 1)
End If
share|improve this answer
+1 Nice one :) You might want to use Mid(cell.Value, 10) instead of Mid(cell.Value, 9, 20) –  Siddharth Rout Feb 17 '13 at 6:56
+1 mid$(cell.value,10) :) –  brettdj Feb 17 '13 at 9:56
Thanks, gents. I refactored the left side of the statement and didn't look at the right. @brettdj: I've heard the mid$ returns a string while the mid returns a variant and that mid$ may perform better. Is that true? –  Jon Crowell Feb 17 '13 at 14:49
This is just what I need. Thank you very much. –  user2079436 Feb 17 '13 at 17:41
That is correct, the string function is quicker than it's variant cousin. Mid can also be used on the LHS not just the RHS (not relevant here, but useful for other applications) –  brettdj Feb 17 '13 at 21:23

Your Answer


