PS Script to delete text in Excel

Hi everyone, newby to PS

Looking for a script that will delete everything after the . in a excel spreadsheet.

I’m only looking for the first row in the spreadsheet and looking to delete in the example below:

google.com -> .com

hp.net -> .net

end result:

google

hp

 

 

 

How about this:

google.com” -Replace(“..$“, “”)
hp.net” -Replace(”..
$”, “”)

In case you also need an example of how to change the second cell on the first row and save to another spreadsheet:

$PathToExcelSpreadsheet = “Your path comes here”
$NewPathToExcelSpreadsheet = “Your path comes here”
$Exceldoc = New-Object -comobject excel.application
$Workbook = $Exceldoc.Workbooks.Open($PathToExcelSpreadsheet)
$Worksheet = $Workbook.Sheets.Item(1)
$NewValue = $Worksheet.Cells.Item(1,2) -replace(“..*$”, “”)
$Worksheet.Cells.Item(1,2) = $NewValue
$Worksheet.SaveAs($NewPathToExcelSpreadsheet)
$Exceldoc.Quit

Mind you: this requires Excel to be installed on the machine where you execute the script (otherwise the Excel.Application won’t be available)

This requirement is a kind of very very special … I could imagine that it’s gonna hard to find an already existing script for this particular task. You will have to piece something together yourself I’m afraid. :wink: :smiley:
I’d recommend to take a look at the great module ImportExcel by Doug Finke. It should make it easy to read and write existing Excel files.

Adding to what @olaf-soyk mentioned, use Import-Excel,Export-Excel cmdlet from ImportExcel module with a ForEach-Object in between to trim what you don’t want.

Import-Excel <file path > | ForEach-Object -Process {
    # use $_.WhatEverColumn and use split or trim
} | Export-Excel <dest file path>