Finding a Character in Excel using [Regex]

I have a list of over 500 new cmdlets for Powershell and SharePoint. I wanted to put this list in an Excel spreadsheet for my own nefarious purposes. When I did that the  Cmdlet name somehow got a number added to the end of it. Like  so:





I was wondering if there is a way, in Powershell to remove these numbers from the name. I tried a few things, with no luck.

Then I did some research online and found that I was over thinking the problem. I just need to split on the first number. But how?

 $Rng1 = $ws1.Cells.Item($i, 2).value()
    $a = $Rng1
    $b = [regex]::split($a,("\d"))  # (“\d”) is a number, so it splits on the first number in the string
    $b[0] # This is everything to the left of the first number
    $ws1.Cells.Item($i, 2).value() = $b[0] # This overwrites the contents of the cell with everything BUT the number

Here is the whole script:

 function Release-Ref ($ref) {

$XLNormal = -4143
$xlPasteValues = -4163          # Values only, not formulas
$xlCellTypeLastCell = 11        # to find last used cell
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2
$xlYes = 1
$xlCellTypeLastCell = 11 

#Create an instance of the Excel.Application
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $"C:\SharePoint Server 2010 PowerShell cmdlets.xlsx")
$ws = $wb.Worksheets.Item(1)
$ws1 = $wb.worksheets | where {$ -eq "sheet1"}
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row + 1
For($i = 8; $i -lt $row; $i++){
   Write-Progress -Activity "Searching $row #'s..." `
     -PercentComplete ($i/$row*100) -CurrentOperation `
     "$i of $Row to go" -Status "Please wait."     
   $Rng1 = $ws1.Cells.Item($i, 2).value()
    $a = $Rng1
    $b = [regex]::split($a,("\d"))
    $ws1.Cells.Item($i, 2).value() = $b[0]
Release-Ref $ws
Release-Ref $wb
Release-Ref $xl

2 thoughts on “Finding a Character in Excel using [Regex]

  1. Pingback: low vitamin d

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s