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

%3CLINGO-SUB%20id%3D%22lingo-sub-1623534%22%20slang%3D%22en-US%22%3ECopy%20data%20in%20an%20excel%20column%20and%20cut%20and%20paste%20into%20a%20new%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623534%22%20slang%3D%22en-US%22%3E%3CP%3EAfternoon%20all%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sure%20this%20is%20possible%2C%20but%20I%20have%20little%20to%20no%20experience%20with%20PS.%26nbsp%3B%20I%20have%20an%20excel%20spreadsheet%20that%20has%20a%20column%20with%20data%20that%20looks%20something%20like%20%22statement%20one%20vs%20statement%20two%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20to%20do%20is%20find%20a%20way%20to%20automate%20deleting%20the%20%22vs%22%20and%20then%20having%20%22statement%20two%22%20cut%20from%20the%20column%20and%20added%20to%20a%20new%20column.%20Basically%20everything%20to%20the%20right%20of%20%22vs%22%20cut%20and%20pasted%20into%20a%20new%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%20on%20how%20this%20can%20be%20done%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20your%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1623534%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1623664%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20data%20in%20an%20excel%20column%20and%20cut%20and%20paste%20into%20a%20new%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623664%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F709650%22%20target%3D%22_blank%22%3E%40DanZ24%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20example%20can%20help%20you.%3C%2FP%3E%3CP%3EThe%20script%20open%20excel%2C%20add%20a%20workbook%2C%20rename%20it%2C%20fill%20five%20cells%20in%20the%20first%20columns%2C%20then%20select%20it%2C%20cut%20%26amp%3B%20paste%20in%20the%20second%20column%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E%24Excel%20%3D%20New-Object%20-Com%20Excel.Application%0A%24Excel.visible%20%3D%20%24True%0A%24wb%20%3D%20%24Excel.Workbooks.Add()%0A%24sheet%3D%24wb.Worksheets.Item(1)%20%0A%24sheet.Name%20%3D%20'Primo'%0A%0A%24Sheet.Cells.Item(1%2C1)%20%3D%20'1'%0Asleep%20-Seconds%202%0A%24Sheet.Cells.Item(2%2C1)%20%3D%20'2'%0Asleep%20-Seconds%202%0A%24Sheet.Cells.Item(3%2C1)%20%3D%20'3'%0Asleep%20-Seconds%202%0A%24Sheet.Cells.Item(4%2C1)%20%3D%20'4'%0Asleep%20-Seconds%202%0A%24sheet.cells.item(5%2C1)%20%3D%20'5'%0Asleep%20-Seconds%202%0A%23%20Select%20%0A%24r1%20%3D%20%24sheet.Range('A1%3AA5')%0Asleep%20-Seconds%202%0A%23%20Cut%0A%24r1.cut()%0Asleep%20-Seconds%202%0A%24r2%20%3D%20%24sheet.Range(%22B1%3AB5%22)%0A%24sheet.Paste(%24r2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

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