Forum Discussion
DanZ24
Aug 31, 2020Copper Contributor
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.
- gastoneBrass Contributor
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)