PS Script to delete text in Excel

Welcome Forums General PowerShell Q&A PS Script to delete text in Excel

This topic contains 3 replies, has 4 voices, and was last updated by

 
Senior Moderator
2 months, 3 weeks ago.

  • Author
    Posts
  • #163055

    Participant
    Topics: 2
    Replies: 0
    Points: 22
    Rank: Member

    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

     

     

     

  • #163064

    Participant
    Topics: 1
    Replies: 15
    Points: 34
    Rank: Member

    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)

  • #163065

    Participant
    Topics: 1
    Replies: 1529
    Points: 2,587
    Helping Hand
    Rank: Community Hero

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

    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. 😉 😀
    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.

  • #163113

    Senior Moderator
    Topics: 8
    Replies: 1041
    Points: 3,439
    Helping Hand
    Rank: Community Hero

    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  | ForEach-Object -Process {
        # use $_.WhatEverColumn and use split or trim
    } | Export-Excel 
    

You must be logged in to reply to this topic.