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

Highlighted
Occasional Contributor

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.

1 Reply
Highlighted

@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)