Issue with importing numbers with Excel accounting format

[wayne-a]

Hi, I'm having a problem importing an excel file where the numbers are formatted as "Accounting" format.  This puts parenthesis around negative numbers.  Upon importing the value it seems to recognize it as a negative and shows it correctly when displayed in a table but doesn't total it, and when multiple spreadsheets are loaded is treating it like character data.  I've tried to put a Num function in the load script with various formats but this hasn't helped.  Thanks in advance for any help.

Cell formatting in Excel

Displayed in Qlik Sense Table,  value displays correctly but totals don't calculate

Load script function (tried with various formats, none worked)

[Vegar]

Try to replace num(...) with num#(...)

[DavidM]

How about this:

If( Left("Period 4 - 2020",1)=chr(40),

PurgeChar(PurgeChar("Period 4 - 2020",chr(40)),chr(41))*(-1),  "Period 4 - 2020") as "Period 4 - 2020"

[wayne-a]

Thanks Vegar and David M.  Unfortunately neither of those worked, even tried combining the ideas by putting a Num# in front of the PurgeChar.  It seems that Qlik recognizes it as a number based on what I see in the data manager but for some reason isn't rendering or summing correctly.  I was able to get it to sum when I chose "Sum" for how to handle the total of that field but when importing multiple worksheets it doesn't handle it correctly.  Thanks again.  

[wayne-a · Accepted solution]

Based on the behavior with the total I decided to change the measure from just the field value to Sum(field) instead.  That worked, and the total started working in auto mode as well.  Very strange but at least found a solution.