Convert fractions into decimals

[Alyssa_Grantham]

Hi all,

I have an app that records mixed numbers and fractions in a text field: i.e. "1/8", "2 7/8", "3" (one eighth, two and seven eighths, three)

I am attempting to transform these fractions into decimal numbers so I can sum them together. i.e. the above would be converted to 0.125, 2.875, 3.000.

I'm starting at the basics to see if I can at least get the fractions by themselves to render, as I only have to account for a small number of allowed fraction inputs.

Here is my formula for the Decimal Pages field.

However, my fractions keep getting validated as a date.

And when I try adding a value on the front end, I get a wildly wrong result.

Has anyone managed to successfully manipulate fractions?

[Suvrutt_Gurjar · Accepted solution]

Yes, there seems to be a problem with fractions when expressed in the format "1/8" in that it always gets recognized as a date.

You could alternatively try the following expression in the [Decimal Pages] field, it should correctly convert all the fractions in the format "N1/N2" as long as both N1 and N2 are single digit integers. If you need more than single digit integers in the fractions format N1/N2, the expression will need a tweak.

Please try the below expression in the [Decimal Credits] column:

(NUMBER(LEFT([Credit Pages],1))*1.000) / (NUMBER(RIGHT([Credit Pages],1)))

Edit: The expression below should work for any number of digits in N1 and N2 in N1/N2

(NUMBER(INDEX(SPLIT([Credit Pages],"/"),1))*1.000 )/
(NUMBER(INDEX(SPLIT([Credit Pages],"/"), COUNT(SPLIT([Credit Pages],"/"))))*1.000)

[Suvrutt_Gurjar · Accepted solution]

Yes, there seems to be a problem with fractions when expressed in the format "1/8" in that it always gets recognized as a date.

You could alternatively try the following expression in the [Decimal Pages] field, it should correctly convert all the fractions in the format "N1/N2" as long as both N1 and N2 are single digit integers. If you need more than single digit integers in the fractions format N1/N2, the expression will need a tweak.

Please try the below expression in the [Decimal Credits] column:

(NUMBER(LEFT([Credit Pages],1))*1.000) / (NUMBER(RIGHT([Credit Pages],1)))

Edit: The expression below should work for any number of digits in N1 and N2 in N1/N2

(NUMBER(INDEX(SPLIT([Credit Pages],"/"),1))*1.000 )/
(NUMBER(INDEX(SPLIT([Credit Pages],"/"), COUNT(SPLIT([Credit Pages],"/"))))*1.000)

[Alyssa_Grantham]

Thanks for the additional option - I still have a ways to go to come up with an expression that will accommodate mixed fractions and whole numbers, but I am very happy the fraction part is dealt with! I'll update this thread with some additional IF logic once I can work out the cleanest way to do it.

[Suvrutt_Gurjar · Accepted solution]

Just in case it helps for numbers in the format  just N1 (8, 10, 221 and so on)  or N1 N2/N3 (2 3/8, 5 7/8, 23 5/11 )  or  N1/N2 ( 4/8. 7/9 , 11/5, 223/13 )  format , you may want to try the expression below

SWITCH(
COUNT(SPLIT(SUBSTITUTE([Credit Pages]," ","/"),"/")),

3,

DECIMAL(INDEX(SPLIT(SUBSTITUTE([Credit Pages]," ","/"),"/"),1))+
(NUMBER(INDEX(SPLIT(SUBSTITUTE([Credit Pages]," ","/"),"/"),2))*1.000/
NUMBER(INDEX(SPLIT(SUBSTITUTE([Credit Pages]," ","/"),"/"),3))*1.000) ,

2,

(NUMBER(INDEX(SPLIT(SUBSTITUTE([Credit Pages]," ","/"),"/"),1))*1.000/
NUMBER(INDEX(SPLIT(SUBSTITUTE([Credit Pages]," ","/"),"/"),2))*1.000),

DECIMAL(INDEX(SPLIT(SUBSTITUTE([Credit Pages]," ","/"),"/"),1))

)

[Alyssa_Grantham]

That's amazing - works perfectly!!!! 😀

I must confess I wasn't sure how to approach a variable number of digits before the "/", and this does it brilliantly.

I had gone with:
NOT (CONTAINS([Credit Pages], "/")), (DECIMAL([Credit Pages]))
for the first scenario... and then I ran into trouble trying to target does contain "/" at specific locations in the string (left/right). I didn't think to use it as a delimiter, which is much more straightforward.

Fabulous answer, many thanks again!

[Suvrutt_Gurjar]

You are welcome. Good to know the expression works as per your requirement.

[Alyssa_Grantham]

Woohoo, thank you very much @Suvrutt_Gurjar - now I'm getting somewhere!

For anyone else interested, I tried workarounds using other field formats (ENUM, NAME etc) and no matter what, if your value is N1/N2 or N1-N2 it gets formatted as a date. The only way seems to be to strip the text string down to its single character elements (as shown above) to make any impact.

[AleksiAlkio]

This should work as well.. 🙂

DECIMAL(INDEX(SPLIT([Text]," "),1))+
DECIMAL(INDEX(SPLIT(INDEX(SPLIT([Text]," "),2),"/"),1))/
DECIMAL(INDEX(SPLIT(INDEX(SPLIT([Text]," "),2),"/"),2))