+ Reply to Thread
Results 1 to 5 of 5

Adding multiple decimal places into a cell

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    1

    Adding multiple decimal places into a cell

    I have a column with 10 digit numbers. I need to insert two decimal places into the number after the fourth and sixth digit. For example:
    8523495000 -> 8523.49.5000
    8443321020 -> 8443.32.1020
    8523493000 -> 8523.49.3000

    Does anyone have a formula that can help add these decimal places?

  2. #2
    Registered User
    Join Date
    01-31-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Adding multiple decimal places into a cell

    Try typing this in a column next to your values (say value 1 is in cell A1):

    =left(A1,4)&"."&mid(A1,5,2)&"."&right(A1,4)

    It'll be in a text format

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Adding multiple decimal places into a cell

    Maybe

    =LEFT(A1,4)&"."&MID(A1,5,2)&"."&RIGHT(A1,4)

  4. #4
    Registered User
    Join Date
    01-30-2013
    Location
    Hobart, TAS
    MS-Off Ver
    Office 2003, XL2007
    Posts
    46

    Re: Adding multiple decimal places into a cell

    If number is to be used as an identifier and not required to do math a custom format may suffice

    Format would be 0000"."00"."0000 (CTRL+1 to enter formatting menu)

  5. #5
    Registered User
    Join Date
    02-15-2022
    Location
    Colorado
    MS-Off Ver
    Microsoft 365
    Posts
    1

    Lightbulb Re: Adding multiple decimal places into a cell

    Thank you so much everyone. This was very helpful to solving my problem. I was attempting to format a series of 10 digit numbers with a preceding 0. For example, 0123456789 to 0123.45.67.89. I first used =TEXT (A1, "0000000000"). Then with your help I used function LEFT and created an additional MID.

    =LEFT(C4,4)&"."&MID(C4,5,2)&"."&MID(C4,7,2)&"."&RIGHT(C4,2)


    Sincerely,

    Vangogh

+ 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