Split out number in parenthesis and replace with a negative sign

I imported some data from a Excel Binary .xlsb file and the column of the deferred cost/ income become V_wstring format. All the negative numbers in parenthesis when I applied the Tonumber (x) formula, all become "null". The data in V_wstring look like below. What can I do to 1) keep positive as positive and 2) split out the negative numbers in parenthesis and put a negative sign for those and 3) convert the data type to Double/ Number?

Or is there any way to import the Excel binary file and not to bring in the data type as V-Wstring and as number?

It's a read only file and for the data auditing purpose, I have to link directly to this file and cannot save the file as other Excel formats and used that as my input. Thanks for the help.

Account	Deferred Cost
1	(123)
2	200
3
4	(800)
5	(90000)
6	300
7	80

Datasets

Input

Parse

Preparation

Expression

Accepted answers

HomesickSurfer

Hi @Ewbkm

Use the following expression for a new column/field in a Formula Tool, setting the Data Type to Int64:

IF 
StartsWith([Deferred Cost], '(') AND EndsWith([Deferred Cost], ')') 
THEN 
ToNumber('-'+REGEX_REPLACE([Deferred Cost],'[[:punct:]]|[\$\+<=>\^`\|~]','')) 
ELSE 
ToNumber([Deferred Cost]) 
ENDIF

All comments

CharlieS

HI @Ewbkm

There's going to be a few ways to do this in Alteryx and I'm sure the Community will chime in with a few options for you. Here's one way you can do this:

You can leave the input as a string, then use some character replacement functions to replace the open parenthesis with a negative sign

Replace([Deferred Cost],"(","-")

Then perform a similar task to replace the close parenthesis with nothing (AKA delete it). Now that you have a string that looks like the negative number you want, convert the field to a numeric field so you're ready to perform numeric functions.

ToNumber(Replace(Replace([Deferred Cost],"(","-"),")",""))

You can do all these steps in one Multi Field Formula tool: apply both character replacements and change the field type. I have attached an example to show how this can be done.

Check it out and let me know if you have any questions, and I'm sure others will chime in with alternative methods too.

20211110-Negative.yxmd

HomesickSurfer

Hi @Ewbkm

Use the following expression for a new column/field in a Formula Tool, setting the Data Type to Int64:

IF 
StartsWith([Deferred Cost], '(') AND EndsWith([Deferred Cost], ')') 
THEN 
ToNumber('-'+REGEX_REPLACE([Deferred Cost],'[[:punct:]]|[\$\+<=>\^`\|~]','')) 
ELSE 
ToNumber([Deferred Cost]) 
ENDIF

Ewbkm

Thank you for the help. I just copied and paste your formula in and from the preview of the formula, it seemed work. But when I ran the workflow, I got the error message as below:

What does that mean "" is missing?

Thanks.

CharlieS

When you pasted that expression into a formula, you might have forgotten to name this new field. Don't forget to also change the field type to a numeric type.

Ewbkm

Thank you very much. I forgot to change the data type.

I am quite new in Alteryx and still have hard time to understand the Regen expression.

'[[:punct:]]|[\$\+<=>\^`\|~]'

Is :punct: for all the punctuations or just for parenthesis and what's \$|+<=> stands for? Thank you so much for your help.

Ewbkm

It works. Thanks.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703