XLS format drops zeros... runs fine in XLSX

[andrewplaice]

Hi all... I imagine the solution to this is over my head, but I'm going bonkers and need help  I created a workflow working from a worksheet in XLSX... turns out the source file is actually XLS.   Everything comes in as VWString and when I convert it to any numerical value of any kind to match the original data format t is dropping the values that are negative (in parenthesis) and making them null. The positive numbers don't drop.

Any suggestions?  Is there a tool to convert the negatives before I make numerical?  

Thanks!

[jacob_kahn]

Hey! I've had this before - I'm trying to think of what my solution was.

Does the data retain a specific data type in Excel?

[mceleavey · Accepted solution]

Hi @andrewplaice ,

You need to replace the parentheses as these will not be recognised as numeric columns.

Use the formula tool (or multi-field formula) and use the following expression:

replace(replace([Field],"(","-"),")","")

This will give the following:

Hope this helps.

M.

[andrewplaice]

Thank you!  Worked perfectly... I was trying but kept getting lost in all the (),,"",))  🙂

Now that I see it in front of me, it make sense.  Appreciate it!

[mceleavey]

Ha.

Glad I could help!

M.