+ Reply to Thread
Results 1 to 4 of 4

Text Function with & => Accounting Format Needed

  1. #1
    Registered User
    Join Date
    07-08-2014
    Location
    Orange County, CA
    MS-Off Ver
    2010
    Posts
    4

    Text Function with & => Accounting Format Needed

    So basically I'm comparing two numbers, estimates vs real figures. I need the real figures to show up with and asterisk. And I need the asterisk to be a value in the function in order to pull through to another tab for an output table (formatting asterisks into the cell will not pull through). Right now my text function reads:

    TEXT(R26,"_($* #,##0.00_)")&"*"

    The problem is this is giving me a currency format still, not an accounting format. So I need the asterisk as a value in the function, in accounting format.

    Can someone help me out with this?

    Thanks,
    Mike

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Text Function with & => Accounting Format Needed

    You are using a custom format string with a "*", which means "use enough spaces to fill the cell." But this only works for custom formatting, not in the TEXT function.

    One way is to write the formula to fill space to the desired number of characters. However, the alignment will only work if you use a fixed-width font, like Courier. This example uses a cell width of 15 characters. Modify the 15 to suit.

    ="$"&REPT(" ",15-1-(LEN(TEXT(R26,"#,##0.00")&"*")))&TEXT(R26,"#,##0.00")&"*"

    If you use a proportional font, like Arial, each number and the spaces take up a different amount of space and it gets very complicated to space things out for the justification used in Accounting format.

    Why do you need to use Accounting format? If it's just for readability maybe you can omit the "$" and then put it by itself in a column to the left.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-08-2014
    Location
    Orange County, CA
    MS-Off Ver
    2010
    Posts
    4

    Re: Text Function with & => Accounting Format Needed

    I have to use accounting format because it is the practice of my group. So since all our presentations are accounting format for every dollar figure shown in a table, I
    can't have any difference. The dollar symbol in its own column is a good simple solution as well.

    Thanks for the help.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Text Function with & => Accounting Format Needed

    It can be formatted in such a way as to deemphasize the fact that it's in its own column. See attached example. The rightmost column is formatted with a white background, which hides the gridlines.
    Attached Files Attached Files

+ 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. Forensic Accounting Formula Help Desperately Needed
    By spurredhal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2013, 04:27 PM
  2. Replies: 1
    Last Post: 11-26-2012, 10:51 PM
  3. Replies: 2
    Last Post: 07-13-2012, 03:40 AM
  4. How to change accounting format on the format axis
    By khoadphamm in forum Excel General
    Replies: 5
    Last Post: 01-26-2011, 10:43 AM
  5. Accounting Guru Needed / Excel or Programming
    By insmarketing in forum Excel General
    Replies: 3
    Last Post: 11-13-2006, 08:02 AM

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