Powershell; writing formuals into Excel

Question

Looking for help;
In using the powershell line to insert a formula, for example:

$formula = “=Unique(B1#)”
$ws.Cells.Item(1,4).Formula = $formula

My formulas all insert with (using the above as the example):
=@unique (B1#)

How can I insert a formula without the ‘@’ being added?
(O365)

Answer 1

Before I touch my Reports spreadsheet, I'm trying to solve this problem on my test sheet with a 'Test' tab in it ... if it helps, my Powershell for this test is;

$xl=New-Object -ComObject Excel.Application
$wb=$xl.WorkBooks.Open('C:\Temp\Excel_Report\Test_Formula.xlsx')
$page1 = 'Test'
$ws = $wb.worksheets | where-object {$_.Name -eq $page1}
$xl.Visible=$false
$formula = "=Unique(B1#)"
$ws.Cells.Item(1,4).Formula = $formula

$wb.Save()
$xl.Quit()

Answer 2

Hi,

The @ is an implicit intersection operator.
https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34?ui=en-US&rs=en-US&ad=US

As the issue is more related to office, please start a new thread with the "office-itpro" tag if you don't want the operator to be added automatically.

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.