XLS format drops zeros... runs fine in XLSX

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!

Regex

Tips and Tricks

Data Investigation

Behavior Analysis

Accepted answers

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.

All comments

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!

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703