Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How can I insert a decimal point back into a string of numbers that differ in length?

Highlighted
6 - Meteoroid

What is a formula I can use to add decimal points 2 digits back of the end of each number?

 

As an example, these are how I want the numbers below displayed:

167.63

1141.23

712.06

etc

 

mattpeltcfa_0-1585316061917.png

 

I am close with this formula, but it only puts the decimal one digit back:

 

ToNumber(REGEX_Replace([Amount],"(?=\d$)","."))

 

Highlighted
15 - Aurora

Why don't you do tonumber([Amount])/100

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @mattpeltcfa 

 

An alternative would be to use the substring functions and edit this as a string, then convert back to a number. Here's the expression:

 

ToNumber(
Left(ToString([Amount]),Length(ToString([Amount]))-2)
+"."+
Right(ToString([Amount]),2)
)

 

 

Highlighted
12 - Quasar
12 - Quasar

@DavidP Loved it. Because the RegEx was given, I started figuring out the RegEx formula.

 

I did not even think of such a simple solution. 

 

Thanks,

Yug

Highlighted
15 - Aurora

@ydmuley , same thing happened to me a couple of weeks ago! It reminded me to look at the problem before thinking about the solution

Highlighted
17 - Castor
17 - Castor

Hi @mattpeltcfa 

 

I'm not a fan of using the Regex hammer unless required, but if you had to, this would do it.  

 

REGEX_Replace([Field1], "\b(\d*?)(\d{2})\b", "$1.$2")

 

It matches any 0 or more digits followed by exactly 2 digits.  The \b on either side match word boundaries to ensure that you're not adding periods before every set of 2 digits in your string

 

Here are the results

 

regex.png

 

Dan

Labels