VBA move character to next column

[jaik22]

I want to write a code that If column A contains REG then move it over to column B and remove it from A.
For example

before
a1 b1
tsf-REG

after

a1 b1
tsf REG


How can I write code for this kind of purpose?

 

[alansidman]

Give this a try

Option Explicit


Sub RegX()
    Dim i As Long, lr As Long, x As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        If InStr(1, Range("A" & i), "REG") > 0 Then
            x = InStr(1, Range("A" & i), "REG")
            Range("A" & i) = Left(Range("A" & i), Len(Range("A" & i)) - 5)
            Range("B" & i) = "REG"
        End If
    Next i


End Sub

 

[jaik22]

Thanks! you rock!

 

[Rick Rothstein]

Give this a try

Option Explicit

Sub RegX()
    Dim i As Long, lr As Long, x As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        If InStr(1, Range("A" & i), "REG") > 0 Then
            x = InStr(1, Range("A" & i), "REG")
            Range("A" & i) = Left(Range("A" & i), Len(Range("A" & i)) - 5)
            Range("B" & i) = "REG"
        End If
    Next i
End Sub

Here is another way to write this macro...

[table="width: 500"]
[tr]
	[td]Sub RegX2()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate(Replace("IF(ISNUMBER(FIND(""-"",A1:A#)),REPLACE(A1:A#,1,FIND(""-"",A1:A#),""""),IF(B1:B#="""","""",B1:B#))", "#", LastRow))
  Columns("A").Replace "-*", "", xlPart
End Sub[/td]
[/tr]
[/table]