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!
Solved! Go to Solution.
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?
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.
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!