Trim a number to just the decimals

[Charity_K_Wilson]

I suspect this can be done with Regex.  I need to get just the decimals.

I'm doing HR Analytics, and looking at employees Years of Service.  We measure Years of Service as 4.92810010910034.  I'm trying to use this field to gauge how close an employee is to their anniversary.  Obviously this person is very close to their anniversary.  So, all I need is the section after the decimal.  

So, how do I trim this number?

[CharlieS · Accepted solution]

Hi @Charity_K_Wilson !

A non-RegEx method would be to use the SubString( function to find the ".". Here's an example, where [YoS] is the "Years of Service" Field. 

SubString(ToString([YoS]),FindString(ToString([YoS]),"."))

I've attached an example of this in action too. 

[Kenda]

Hi @Charity_K_Wilson 

There are a couple ways to accomplish this depending on how many of those decimal places you want to keep after the decimal point. 

One way to do this would be to use a Formula tool with this expression:

Substring(tostring([Field1]),FindString(tostring([Field1]), ".")+1,4)

This will keep the first four digits after the decimal point.

If you wanted to try your hand at RegEx, you could also use a Formula tool with this expression:

REGEX_Replace(tostring([Field1]), ".*\.(\d+)", "$1")

This will keep all digits after the decimal point.

Note - in both of these cases, I am assuming your original field is a numeric type so I wrap that field name in tostring() so that I can use it in these expressions. 

Hope this helps!

[Charity_K_Wilson]

Your assumptions were right!  Thank you for your help.

And so neat to see the regex way of doing it.  

[Charity_K_Wilson]

Thanks Charlie.  That did exactly what I needed it to do.  I knew there had to be a quick formula way to do it.

[compunut]

You could simply use the FLOOR function
[Number] - Floor([Number])