+ Reply to Thread
Results 1 to 6 of 6

Custom Formatting Cell with 2 numbers

  1. #1
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    510

    Custom Formatting Cell with 2 numbers

    Hi and thanks for looking at my question, which is.

    I would like to enter 2 numbers in the same cell i.e;

    300-900

    and have it format to

    3:00-9:00

    I can use the following for single number entries

    Please Login or Register  to view this content.
    i.e; 300 would format to 3:00

    any help or pointers would be helpful

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.91 for Mac MS 365
    Posts
    8,591

    Re: Custom Formatting Cell with 2 numbers

    if 300-900 is already in the cell, then this should work... =LEFT(A2,1)&":"&MID(A2,2,4)&":"&RIGHT(A2,2)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Custom Formatting Cell with 2 numbers

    =concatenate(left(a2,1),":",mid(a2,2,2),"-",mid(a2,5,1),":",right(a2,2))

  4. #4
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    510

    Re: Custom Formatting Cell with 2 numbers

    Hi I would like to format the cell that the actual numbers are in using Format cell option

    both your formulas work however not on the original cell

    thanks

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.91 for Mac MS 365
    Posts
    8,591

    Re: Custom Formatting Cell with 2 numbers

    let me see if I have this straight, you have, in one cell 300-900 and you want that converted in another cell to 3:00-9:00 AND you want either or both to be formatted as numbers, is that right? You can have them individually in cells (say A2 has 300 in it and B2 has 900 in it) as numbers but I don't believe it is possible to have 3:00-9:00 in one cell as a number nor 300-900 as a number. Am I mistaken about what you want?

  6. #6
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    510

    Re: Custom Formatting Cell with 2 numbers

    You are correct that I want eg 300-900 converted to 3:00-9:00

    I can format the cell with say 300 converted to 3:00 by using Format Cell "Custom" option and using

    Please Login or Register  to view this content.
    I thought that I could have used

    Please Login or Register  to view this content.
    but not that simple
    Last edited by Toonies; 05-05-2014 at 02:47 PM. Reason: Custom not Special

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formatting Numbers into millions using custom function
    By derivative_x in forum Excel General
    Replies: 2
    Last Post: 06-19-2012, 01:50 PM
  2. Custom formatting combining text and numbers
    By Gryphen957 in forum Excel General
    Replies: 3
    Last Post: 02-01-2012, 11:11 AM
  3. Custom Formatting for Cells with Numbers and A Letter
    By flipjarg in forum Excel General
    Replies: 5
    Last Post: 01-14-2009, 04:00 PM
  4. Custom formatting of Numbers
    By paish in forum Excel General
    Replies: 1
    Last Post: 11-02-2007, 02:25 AM
  5. [SOLVED] Custom Formatting numbers question
    By Junkyard Engineer in forum Excel General
    Replies: 2
    Last Post: 07-14-2005, 04:05 PM

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