+ Reply to Thread
Results 1 to 9 of 9

Custom number formating

  1. #1
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Custom number formating

    Hello again

    I want to display negative number as i.e (-275 as 275 Cr. hiding the (-) sighn and not removing it and without making it text so that the calculations can be performed on them

    and about positive number i like to display as i.e ( 150 as 150 Dr. without making it text

    Thanks

    Best Regards
    Imran Bhatti
    Teach me Excel VBA

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Custom number formating

    you cannot display Dr / Cr as anything other than text.

    Format would be of the form 0000 "Dr" / 0000 "Cr"

    Stand corrected: the above works!!
    Last edited by JohnTopley; 12-01-2016 at 09:38 AM.

  3. #3
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Custom number formating

    Try a Custom code of
    Please Login or Register  to view this content.
    However, I missed the important point of this becoming Text, as John says above
    If someone has helped you then please add to their Reputation

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Custom number formating

    Apply a Custom Format to the cell(s), using this format string:

    0" Dr";0" Cr";0;@

    Hope this helps.

    Pete

  5. #5
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Custom number formating

    It definitely helped sir Pete_UK. and here are the results after applying basic mathematical operations on the formated cells.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Custom number formating

    How to apply
    1)
    Please Login or Register  to view this content.
    in the worksheet change event or in some other places?

    the text of the code gets red (means syntex error

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Custom number formating

    Why not simply Custom Format the range A1:A100....

    [>=0]#,##0.00" Dr";[<0]##,##0.00" Cr";General

  8. #8
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Custom number formating

    Why not simply Custom Format the range A1:A100....

    [>=0]#,##0.00" Dr";[<0]##,##0.00" Cr";General
    This format alos formates the zero values while the zero is neither Dr nor Cr in Accounting. But the solution of sir Pete_UK is not formating the zero values as Dr or Cr.

  9. #9
    Forum Contributor
    Join Date
    02-07-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    290

    Re: Custom number formating

    Hi,

    How do you want zero values to be formatted?

    Just update the red highlighted portion of the format given by Pete:
    0" Dr";0" Cr";0;@

    for example:
    0" Dr";0" Cr";"Zero";@
    0" Dr";0" Cr";;@
    0" Dr";0" Cr";"Zero Dr";@
    0" Dr";0" Cr";"Zero Cr";@

    Regards,
    Khalid

+ 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. Custom formating
    By InvalidTxtString in forum Excel General
    Replies: 2
    Last Post: 08-24-2016, 02:30 PM
  2. Custom date formating
    By carbon_kid85 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-07-2014, 10:34 AM
  3. Replies: 0
    Last Post: 06-12-2013, 10:54 AM
  4. Custom Cell formating
    By maacmaac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2008, 09:18 AM
  5. custom formating
    By bob12 in forum Excel General
    Replies: 3
    Last Post: 01-26-2007, 01:04 PM
  6. custom formating text
    By Mart in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2005, 09:05 AM
  7. Custom formating won't work
    By Marty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-06-2005, 12:06 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