Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

REGEX help for numeric field

vaibhav_jain
8 - Asteroid

Hi, Any idea how to get the desired result using regex. some of the numeric field having minus sign at the right hand side.

 

Want to shift the sign to left side. 

 

Input - Numeric field : 872.68-

Required output :  -872.68

 

Any suggestion..

 

10 REPLIES 10
rarmstrong
8 - Asteroid

Is it formatted as a String because the '-' is at the end? You can do this with a formula.

 

"-" + Replace([Field1],"-","")

 

vaibhav_jain
8 - Asteroid

Thanks !! any idea of doing through Regex as well.

CharlieS
17 - Castor
17 - Castor

@vaibhav_jain

 

Using a Formula tool:

"-"+trimright([Input],"-")

 

Using RegEx tool:

Parse: (\d+.\d+)

Replace: -$1

 

I have attached an example of both. Let me know if you have any questions.

rarmstrong
8 - Asteroid

Here is the RegEx method

 

capture.PNG

vaibhav_jain
8 - Asteroid

Thanks for the revert. 

 

If there is any special character coming after the number like 2345.87$ or 2345.87* hoping it will work & remove special characters as well ?

danrh
13 - Pulsar

Are you trying to get rid of the special characters, or move them to the beginning as well?  To get rid of them, use:

REGEX_Replace([Field1], '(\d+\.\d+)(-)?.*', '$2$1')

If there is a dash after the number, it will move it to the beginning.  Otherwise, it will remove everything after the number.  If you want to move the next character to the beginning no matter what, use:

REGEX_Replace([Field1], '(\d+\.\d+)(.).*', '$2$1')

Hope it helps!

vaibhav_jain
8 - Asteroid

HI Quasar,

 

Thanks for sharing the Regex.

 

One last thing, if we want all the special character to be removed from any where in the amount field. For example 2,3@4$5.56-  then how do we remove it. Keeping in mind to add "-" sign for negative values in the beginnings.

 

Would be great help if you could help.

danrh
13 - Pulsar

There might be a way to do this in one go, but I'd probably use some nested RegEx for this:

REGEX_Replace(REGEX_Replace([Field1], '[^\d\.-]', ''),'(\d+\.?\d*)(-)?','$2$1')

The inside RegEx removes anything that's not a digit, period, or dash.  Then the outside RegEx moves the dash to the front, if it exists.  After looking at my previous answer, I tweaked the inside portion a little to allow for numbers without decimals as well.

aidokoakoh001
5 - Atom

Hi,

 

I need help with a formula  or Regex on how to sepearte the numbers in decimal places and the following senntence which are all in a column. I want them to be in seperate columns rather than same column. Thank you

Example..  The following data is in one column

5.1.1.1 Retrive code from data

5.22.1  Do not retrive code

5.1.2    Retrive code and deposit

Labels