REGEX help for numeric field

[vaibhav_jain]

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..

[rarmstrong]

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

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

[vaibhav_jain]

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

[CharlieS]

@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]

Here is the RegEx method

[vaibhav_jain]

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]

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]

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 · Accepted solution]

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]

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