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:

Backup-SPFarm12

Backup-SPSite13

Clear-SPLogLevel14

Clear-SPMetadataWebServicePartitionData15

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) {
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
} 

$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 = $xl.Workbooks.open("C:\SharePoint Server 2010 PowerShell cmdlets.xlsx")
$ws = $wb.Worksheets.Item(1)
$ws1 = $wb.worksheets | where {$_.name -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"))
    $b[0]
    $ws1.Cells.Item($i, 2).value() = $b[0]
} 
[void]$ws1.cells.entireColumn.Autofit() 
$wb.Save()
Release-Ref $ws
Release-Ref $wb
$xl.Quit()
Release-Ref $xl
Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s