How do I format a number cell to Accounting in Excel using Powershell?

I have a Powershell script that creates an Excel spreadsheet. I would like to format some of the cells with numbers to Accounting with 0 decimal places and no symbol. How do I do this? For example, how would I change the format to Accounting for the numbers in column 2 on rows 7-12 in my snippet code below?
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel.DisplayAlerts = $False
$Wkbk = $Excel.Workbooks.Add()
$Sheet = $Wkbk.Worksheets.Item(1)

$Sheet.Cells.Item(1,1) = "ID Admin"
$Sheet.Cells.Item(2,1) = "Outstanding Task Summary"
$Sheet.Cells.Item(3,1) = "Date: " + $Date
$Sheet.Cells.Item(7,2) = $disableuser_1days
$Sheet.Cells.Item(8,2) = $disableuser_2days
$Sheet.Cells.Item(9,2) = $disableuser_3days
$Sheet.Cells.Item(10,2) = $disableuser_4days
$Sheet.Cells.Item(11,2) = $disableuser_5days
$Sheet.Cells.Item(12,2) = $disableuser_gt5days

$Range = $Sheet.Range("A1:H1")
$Range.MergeCells = $true
$Range = $Sheet.Range("A2:H2")
$Range.MergeCells = $true
$Range = $Sheet.Range("A3:H3")
$Range.MergeCells = $true
$intRow = 7
	Do {
	$Sheet.Cells.Item($intRow,2).HorizontalAlignment = -4108
	$Sheet.Cells.Item($intRow,2).VerticalAlignment = -4160
	} Until ($intRow -gt 12)
$WorkBook = $Sheet.UsedRange


Open in new window

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
I suspect:
$Sheet.Range("B7:B12").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
reason4xistenceAuthor Commented:
Wow! I am going to look up NumberFormat. That doesn't look like normal Powershell regular expressions. At any rate, it works perfectly. Thanks so much, rorya!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.