+ Reply to Thread
Results 1 to 3 of 3

Changing display of value with multiple decimal points

  1. #1
    Registered User
    Join Date
    12-16-2010
    Location
    Columbia, MD
    MS-Off Ver
    Excel 2007
    Posts
    1

    Changing display of value with multiple decimal points

    I inherited a list with 2800 records, each identified with 3 decimals. For example. 1.1.2.4 or 1.2.4.23, or 10.15.123.43.

    I would like to display the items consistently in the following format ##.##.##.###. So, my first entry above would display as .01.01.02.004.

    Is that possible? I have tried many formatting options, but then I realized the 1.1.2.4 is not really a number, because numbers have only one decimal point. Any help is appreciated. i really don't want to enter all 2800 cells again.

    Larry

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Changing display of value with multiple decimal points

    There might be a shorter way..but

    If you put this formula in an adjacent cell:

    Please Login or Register  to view this content.
    Where A1 represents first cell to parse.

    Then copy down to convert all,

    Then you can copy this column and paste special >> Values over your original column and delete the formula column.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Changing display of value with multiple decimal points

    If by any chance you meant 00.00.000.000 (given the last of your examples has three digits in third sub section) then another alternative might be to convert the original to a number and apply a Custom Format

    Assume original values in A1, A2 etc then:

    Please Login or Register  to view this content.
    However, I suspect the above fails requirements... thought I'd post it just in case.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1