I need to change commas to dot in an excel file using Powershell

Question

I need someone to help me solve this problem. I need to write a powershell script which changes the commas of the excel file to dots. The commas are between the numbers.
The table is built up like this:

user name user surname count count etc
example example 13,00 5,00 etc

I don't have any idea how to do it. I hope someone can help me.

Thank you in advance

Answer 1

If you're asking about how Excel represents decimal numbers, or how you can change that, see here: change_the_decimal_point_to_a_comma_or_vice_versa.html

Answer 2

Hi,
The script could be like this

$File = "D:\test.xlsx"
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($File)
$Worksheet = $Workbook.Worksheets.Item(1)
[void]$Worksheet.Cells.Replace(",",".")

$Workbook.Save()
$Workbook.Close()
$Excel.Quit()
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel)

This article is helpful.
https://www.zacoding.com/en/post/powershell-excel-replace-text/

Best Regards,

Ian

Please remember to "Accept Answer" and upvote if the reply is helpful.

Answer 3

Add this to the code given by IanXue-MSFT:

$worksheet.Columns("YourColumnIDHere").NumberFormat = "General"