how to move the specific text on column A to another column

[Carly Lam]

Hello, there are serval question i need help from VBA

1. need to insert one column between column A & B
my answer : Columns("B").Insert

2. if column A cells have text include "up" and "down" will insert to columns C

3. move the last 3 digits of number in column A to column B, and remove the "-" in column A

i hope u can get what i want,,,,,,below picture for your reference, thanks all!!!

Previous

Result

 

[Trebor76]

Hi Carly,

Welcome to MrExcel!!

Try this:

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim i As Long
    Dim strTemp As String
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Sheets("Sheet1") '<-Sheet name containing data. Change to suit if necessary.
    ws.Columns("B").Insert
    For i = 1 To ws.Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If InStr(ws.Range("A" & i), "-") > 0 Then
            strTemp = ws.Range("A" & i)
            ws.Range("A" & i) = Split(strTemp, "-")(0)
            ws.Range("B" & i) = Split(strTemp, "-")(1)
            If InStr(ws.Range("B" & i), " ") > 0 Then
                strTemp = ws.Range("B" & i)
                ws.Range("B" & i) = Split(strTemp, " ")(0)
                ws.Range("C" & i) = Split(strTemp, " ")(1)
            End If
        End If
    Next i
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Carly Lam]

Hi Carly,

Welcome to MrExcel!!

Try this:

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim i As Long
    Dim strTemp As String
   
    Application.ScreenUpdating = False
   
    Set ws = ThisWorkbook.Sheets("Sheet1") '<-Sheet name containing data. Change to suit if necessary.
    ws.Columns("B").Insert
    For i = 1 To ws.Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If InStr(ws.Range("A" & i), "-") > 0 Then
            strTemp = ws.Range("A" & i)
            ws.Range("A" & i) = Split(strTemp, "-")(0)
            ws.Range("B" & i) = Split(strTemp, "-")(1)
            If InStr(ws.Range("B" & i), " ") > 0 Then
                strTemp = ws.Range("B" & i)
                ws.Range("B" & i) = Split(strTemp, " ")(0)
                ws.Range("C" & i) = Split(strTemp, " ")(1)
            End If
        End If
    Next i
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

thank you Robert!!!!

i tried and it works!!! thank you very much

 

[Trebor76]

You're welcome