Number Format '# ##0' set with error

[Shlepok]

I'm trying to change the format of numbers in cells using the command

Set-Format -Worksheet $xl.Лист1 -Range "i4:i500" -NumberFormat "# ##0"

, but when opening the file, the format is set with an error "#\ ##0"

How I can remove this extra slash?

[dfinke]

Not a format I am familiar with. If you create a sheet of data with Export-Excel, and then apply that as a custom format in Excel, does that work?

[dfinke]

This worked for me

$data = ConvertFrom-Csv @"
Region,State,Units,Price
East,Alaska,853,851.63
North,Massachusetts,629,149.25
South,Maine,982,355.76
West,Nebraska,222,618.85
West,Kansas,225,625.41
East,Massachusetts,451,547.41
South,California,272,846.64
North,New Jersey,777,378.07
East,South Carolina,879,259.77
North,Nebraska,673,980.02
North,Massachusetts,421,043.15
North,New York,660,757.04
North,Illinois,952,958.60
South,Kentucky,071,412.85
North,Connecticut,111,863.75
"@

Remove-Item ./test.xlsx

$xlpkg = $data | Export-Excel ./test.xlsx -PassThru
Set-ExcelRange -Worksheet $xlpkg.Sheet1 -Range "d2:d16" -NumberFormat "# ##0"
Close-ExcelPackage $xlpkg -Show

PS \> get-module -list ImportExcel

    Directory: D:\OneDrive\Documents\PowerShell\Modules

ModuleType Version    PreRelease Name                                PSEdition ExportedCommands
---------- -------    ---------- ----                                --------- ----------------
Script     7.1.2                 ImportExcel                         Desk      {Add-ConditionalFormatting, Add-ExcelChart, Add-ExcelDataValidationRule, Add-ExcelNam…

PS \> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.1.3
PSEdition                      Core
GitCommitId                    7.1.3
OS                             Microsoft Windows 10.0.19043
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

[Shlepok]

Not work for me

PS C:\Users\Alex> get-module -list ImportExcel

    Directory: C:\Users\Alex\Documents\PowerShell\Modules

ModuleType Version    PreRelease Name                                PSEdition ExportedCommands
---------- -------    ---------- ----                                --------- ----------------
Script     7.1.2                 ImportExcel                         Desk      {Add-ConditionalFormatting, Add-ExcelChart, Add-ExcelDataValidationRule, Add-ExcelName…}

    Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version    PreRelease Name                                PSEdition ExportedCommands
---------- -------    ---------- ----                                --------- ----------------
Script     7.1.1                 ImportExcel                         Desk      {Add-ConditionalFormatting, Add-ExcelChart, Add-ExcelDataValidationRule, Add-ExcelName…}

PS C:\Users\Alex> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.2.0-preview.3
PSEdition                      Core
GitCommitId                    7.2.0-preview.3
OS                             Microsoft Windows 10.0.19042
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

About Microsoft Excel

Microsoft® Excel® 2019 MSO (16.0.13929.20360), 64-разрядная версия 
Код продукта: 00404-47583-99050-AA788
Идентификатор сеанса: F654AADA-15F3-45D1-BDCC-FAECF94F0CED

Not a format I am familiar with. If you create a sheet of data with Export-Excel, and then apply that as a custom format in Excel, does that work?

Yes, it work if apply custom format in Excel manual

[Shlepok]

I think that for some unknown reason, spaces are escaped after #

Set-ExcelRange -Worksheet $xlpkg.Sheet1 -Range "d2:d16" -NumberFormat '# # #0'

[Shlepok]

I figured it out. This error is associated with the Russian localization of Office 2019, in which the standard number formats were changed to regional. The format '# ##' in the Russian version has the meanings NumFmtID = 3, and in the English version it is the format '#, ## 0'. Accordingly, when exporting data using the module, the '# ## 'format has NumFmtID = 164 and is displayed incorrectly. When setting the format '#, ## 0', everything is displayed correctly. Thanks to the localizers :(

[dfinke]

Glad you found it. I was going add "localization" as an issue. So you "worked around" it using '#, ## 0'? Not sure if there is a way to handle localization there.

[Shlepok]

Yes, the NumFmtID of the "#, ##0" format corresponds to the NumFmtID of the format I need. It is not clear why the comma was replaced with a space in the localization.