PS Script to delete text in Excel

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

  • This topic has 3 replies, 4 voices, and was last updated 1 year ago by
    Senior Moderator
    .
Viewing 3 reply threads
  • 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: 2
      Replies: 24
      Points: 87
      Helping Hand
      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: 5
      Replies: 2368
      Points: 5,987
      Helping Hand
      Rank: Community MVP

      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: 9
      Replies: 1236
      Points: 4,443
      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 <file path > | ForEach-Object -Process {
          # use $_.WhatEverColumn and use split or trim
      } | Export-Excel <dest file path>
      
Viewing 3 reply threads
  • The topic ‘PS Script to delete text in Excel’ is closed to new replies.