Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: Extract fraction as a whole number

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    667
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Extract fraction as a whole number

    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

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Extract fraction as a whole number

    Quote Originally Posted by YasserKhalil View Post
    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
    Is your example the text in one single cell or are those dashes supposed to represent column breaks between different cells? If the latter, what is your cells formatted as in order to retain the trailing zeroes?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    667
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract fraction as a whole number

    These values are numeric in range("A1:A4") not a text but I want to extract the whole fraction

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Extract fraction as a whole number

    Quote Originally Posted by YasserKhalil View Post
    These values are numeric in range("A1:A4") not a text but I want to extract the whole fraction
    If they are not text, then how are the trailing zeroes being shown?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    Jun 2010
    Posts
    667
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract fraction as a whole number

    Quote Originally Posted by Rick Rothstein View Post
    If they are not text, then how are the trailing zeroes being shown?
    Different formats sir
    I know it is weird but these values formatted diffenetly not the same

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Extract fraction as a whole number

    Quote Originally Posted by YasserKhalil View Post
    Different formats sir
    I know it is weird but these values formatted diffenetly not the same
    I don't get it... if they are not text, then they are numbers and numbers do not retain trailing zeroes after a decimal point. The only way to get a number to display trailing zeroes is to format the cell, but the value in the cell would still be a number without a trailing zero... besides cell formatting cannot be being used because the number of digits after the decimal point is not constant. So I don't get it. You might try this formula, but I do not hold out hope that trailing zeroes will be preserved...

    =MID(A1,FIND(".",A1)+1,99)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular
    Join Date
    Jun 2010
    Posts
    667
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract fraction as a whole number

    Is there a function that can split the number to whole number and the fraction as a whole numer..
    I hope to find a way to solve this problem...
    The problem now is the trailing zeroes !!
    Simply these are numbers but I want to treat them as text and get my results

  8. #8
    Board Regular
    Join Date
    Jun 2010
    Posts
    667
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract fraction as a whole number

    There is no way to do that!!!

  9. #9
    Board Regular
    Join Date
    Jun 2010
    Posts
    667
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract fraction as a whole number

    Please there must be a way to get the fraction as it is!!

  10. #10
    New Member
    Join Date
    Oct 2014
    Location
    Mumbai
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract fraction as a whole number

    Quote Originally Posted by YasserKhalil View Post
    Please there must be a way to get the fraction as it is!!

    Hi,

    try below formula,

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

    regards,
    Amitkumar J.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •