Extract fraction as a whole number

[YasserKhalil]

Hello
I want to extract just the fraction as a whole numer even if the fraction has a zero at its end
Example
16.1500 - 12.140 - 10.01 - 60.0060

The results should be

1500 - 140 - 01 - 0060

 

[YasserKhalil]

Hi,

try below formula,

=RIGHT(J3,LEN(J3)-FIND(".",J3))

regards,
Amitkumar J.

Thanks sir for offering help
It doesn't work well for me .. The problem with zero in the fraction is still there

 

[mikerickson]

The problem with the whole scheme is that is is entirely text based. If we try to do this with numbers, since 014 = 14 there is no way to tell the difference between the results of 3.014 and 3.14.

If you want this to work with numbers, change the goal to something like "number of ten thousandths" rather than "fractional part as whole number"

 

[mikerickson]

Perhaps something like =TEXT(mod(a1,1)*100000,"00000")

 

[YasserKhalil]

I didn't find any acceptable solution for that problem

 

[Rick Rothstein]

Different formats sir
I know it is weird but these values formatted diffenetly not the same

So you are saying each cell is formatted individually with no regard to the formatting used on neighboring cells. Okay, see if this formula works for you...

=LEFT(MID(F1,FIND(".",F1&".")+1,99)&"000000000000000",MID(CELL("Format",F1),2,2))

 

[Bhamori]

Yeah, the trailing zeros are a bit tricky. You might want to try out this one:

=+CONCATENATE(MID(A1;FIND(".";A1)+1;LEN(A1));REPT("0";MAX(0;MID(CELL("format";A1);2;1)-LEN(MID(A1;FIND(".";A1)+1;LEN(A1))))))

Assuming that cell A1 has a properly formatted number, that has a fraction part. Otherwise it will yell. Ugly as hell.

Looking forward to Your feedback!

Regards, Balázs

 

[Bhamori]

I was slow

 

[YasserKhalil]

So you are saying each cell is formatted individually with no regard to the formatting used on neighboring cells. Okay, see if this formula works for you...

=LEFT(MID(F1,FIND(".",F1&".")+1,99)&"000000000000000",MID(CELL("Format",F1),2,2))

Works like a charm
Thanks a lot for your great help
Thank you very much Mr. Rick

 

[YasserKhalil]

Yeah, the trailing zeros are a bit tricky. You might want to try out this one:

=+CONCATENATE(MID(A1;FIND(".";A1)+1;LEN(A1));REPT("0";MAX(0;MID(CELL("format";A1);2;1)-LEN(MID(A1;FIND(".";A1)+1;LEN(A1))))))

Assuming that cell A1 has a properly formatted number, that has a fraction part. Otherwise it will yell. Ugly as hell.

Looking forward to Your feedback!

Regards, Balázs

Thank you sir for your helpful formula.. It is very good with decimals only but with the numbers without fractions returns Value error

 

[shg]

UDF?

Function YK(r As Range) As String
    Dim s As String

    s = r(1).Text
    If InStr(s, ".") Then YK = Mid(s, InStr(s, ".") + 1)
End Function

Row\Col	A​	B​	C​
1​	16.1500​	1500	B1: =yk(A1)
2​	-12.140​	140
3​	-10.01​	01
4​	-60.0060​	0060