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
    • #163055
      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: -> .com -> .net

      end result:






    • #163064
      Topics: 2
      Replies: 24
      Points: 87
      Helping Hand
      Rank: Member

      How about this:

      “” -Replace(“\..*$”, “”)
      “” -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

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