Forum Discussion

DanZ24's avatar
DanZ24
Copper Contributor
Aug 31, 2020

Copy data in an excel column and cut and paste into a new column

Afternoon all:

 

I'm sure this is possible, but I have little to no experience with PS.  I have an excel spreadsheet that has a column with data that looks something like "statement one vs statement two".

 

What I need to do is find a way to automate deleting the "vs" and then having "statement two" cut from the column and added to a new column. Basically everything to the right of "vs" cut and pasted into a new column.

 

Any thoughts on how this can be done?

 

Thanks in advance for your time.

  • gastone's avatar
    gastone
    Brass Contributor

    DanZ24 

    I hope this example can help you.

    The script open excel, add a workbook, rename it, fill five cells in the first columns, then select it, cut & paste in the second column

     

    $Excel = New-Object -Com Excel.Application
    $Excel.visible = $True
    $wb = $Excel.Workbooks.Add()
    $sheet=$wb.Worksheets.Item(1) 
    $sheet.Name = 'Primo'
    
    $Sheet.Cells.Item(1,1) = '1'
    sleep -Seconds 2
    $Sheet.Cells.Item(2,1) = '2'
    sleep -Seconds 2
    $Sheet.Cells.Item(3,1) = '3'
    sleep -Seconds 2
    $Sheet.Cells.Item(4,1) = '4'
    sleep -Seconds 2
    $sheet.cells.item(5,1) = '5'
    sleep -Seconds 2
    # Select 
    $r1 = $sheet.Range('A1:A5')
    sleep -Seconds 2
    # Cut
    $r1.cut()
    sleep -Seconds 2
    $r2 = $sheet.Range("B1:B5")
    $sheet.Paste($r2)

     

     

Resources