+ Reply to Thread
Results 1 to 9 of 9

Help with Custom Formatting

  1. #1
    Registered User
    Join Date
    01-16-2017
    Location
    Cape Town, South Africa
    MS-Off Ver
    2016
    Posts
    51

    Help with Custom Formatting

    Hi There

    Please help me custom format the attached file - I have tried to create a custom number format which I can drag down (i.e. paste special - format) but for some reason both Cells A3 & A4 do not display the "Ft (short for feet) correctly even though the final sum total (cell A5) does display correctly. Any help would be appreciated - thanks!

    Regards

    SilverBullet17
    Attached Files Attached Files
    Last edited by SilverBullet17; 01-16-2017 at 02:15 PM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,338

    Re: Help with Custom Formatting

    A5 didn't display 51.600 SF when I opened the file.

    Your custom format doesn't contain "SF" it contains "FT"
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-16-2017
    Location
    Cape Town, South Africa
    MS-Off Ver
    2016
    Posts
    51

    Re: Help with Custom Formatting

    Thanks Richard, you were correct (I've edited accordingly).

    What I am trying to figure out is how to display cells A3 and A4 as 45,000 FT and 5,000 FT accordingly (I could merely add another 0 i.e. ##,#000 " FT" as a once-off) but I cannot use this same formula for Cell A2 (if I did, it would show 1,6000 instead of the 1,600). As such I am looking for a standard formatting formula which would allow me to format Cell A2 to display 1,600 and then copy this custom format (drag down) to Cells A3 and A4 which would hopefully then display 45,000 FT and 5000 FT instead of 45,00 FT and 5,00 FT respectively.

    I hope this explains it better - apologies for the confusion.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,488

    Re: Help with Custom Formatting

    Try a format string like this:

    #,##0" FT"

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    01-16-2017
    Location
    Cape Town, South Africa
    MS-Off Ver
    2016
    Posts
    51

    Re: Help with Custom Formatting

    Hi Pete

    Thanks for the help I have in fact tried that exact formula but it doesn't seem to work - it gives me option 1 in attached spreadsheet. I have also tried Option 2 but am actually looking for Option 3.

    Do you think there may me something wrong/inconsistent with my excel number settings? If so what would you recommend I change?

    Thanks!
    Attached Files Attached Files

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,488

    Re: Help with Custom Formatting

    Your Option 3 is exactly what I see with the format string that I suggested.

    I had assumed that in South Africa your Regional settings were similar to those in the UK, i.e. that you use a full-stop for a decimal point, and commas are used as a thousands separator - is that not the case?

    Pete

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,879

    Re: Help with Custom Formatting

    Part of my confusion on this one is trying to be internationally aware -- Are you using "," as a thousands separator or as a decimal point (I recognize that much of this is that I am not usually "internationally aware" so I am very used to US conventions).

    What I see in your file (after Excel switches for my local decimal and thousand's separator settings) is that you are trying to display numbers in the thousands as fractional thousands. The format string as I opened it was ##.#00," FT" The comma tells Excel to scale the displayed number by 1000, the 0's tell Excel to display those digits even if they are insignificant zeros, and the #'s tell Excel to only display the values that are significant, but to not display insignificant zeros. (https://support.office.com/en-us/art...2-09fab54be7f4 help file describing number format codes). So I see 45E3 displayed as 45.00, where Excel displays (as the format code instructs) two of the three insignificant zeroes to the right of the decimal point. I would probably change the third digit to the right of the decimal point to a 0 instead of #, so that Excel will display all three insignificant zeroes to the right of the decimal point (I usually like to see the insignificant zero to the left of the decimal point for numbers between 0 and 1, so I would probably change that as well). I would probably use something like 0.000," FT" (using . for decimal separator and , for thousands separator/scale display by 1000 as I am used to doing. Adapt for your decimal/thousands separator settings).

    If I have completely misunderstood what you want, please clarify, and we'll help you come up with what you really want.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Registered User
    Join Date
    01-16-2017
    Location
    Cape Town, South Africa
    MS-Off Ver
    2016
    Posts
    51

    Re: Help with Custom Formatting

    Hi Pete

    Thank the lord - for some reason, the South African decimal point was a comma (changed it to a full stop now and it worked) - thanks a mil!!!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,488

    Re: Help with Custom Formatting

    You're welcome - glad to help. Thanks for the rep.

    Pete

+ 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. Help with Custom Formatting
    By beaglesBuddy in forum Excel General
    Replies: 3
    Last Post: 12-02-2014, 11:41 AM
  2. Custom formatting issue using conditional formatting
    By wrongway15 in forum Excel General
    Replies: 2
    Last Post: 07-13-2014, 03:18 PM
  3. [SOLVED] Formatting Numarical Values with Custom Formatting
    By excelneub in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-11-2014, 04:06 PM
  4. custom formatting
    By rehan0129 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2010, 08:37 AM
  5. Custom Formatting
    By MatthewFlinchem in forum Excel General
    Replies: 1
    Last Post: 03-09-2009, 02:15 PM
  6. Formatting the result of a VLookup / Custom Formatting
    By Jon__Matthews@lycos.co.uk in forum Excel General
    Replies: 1
    Last Post: 03-10-2006, 11:30 AM
  7. Custom Formatting
    By nastech in forum Excel General
    Replies: 5
    Last Post: 01-26-2006, 11:55 PM

Tags for this Thread

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