Results 1 to 3 of 3

Thread: Leading characters - Negative number and comma problem

  1. #1

    Leading characters - Negative number and comma problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a column of variable length numbers, positive and negative.
    I need to place leading "+" signs in each of the cells to create a standard length of 15 characters.

    For instance;
    My A1 has value: -2,100.31
    I need it to a display as: ++++++-2,100.31

    The formula that I tried at cell B1 is : =REPT("+",15-LEN(A1))&A1.
    Works with negative and positive numbers, but I can't get the thousands and decimal separators "," and "." to appear.

    Then, in vain I tried custom formatting cell A1 as : +++++++#,##0.00. (I know it's not the same and is a hassle with different length numbers)
    This displays the positive numbers as I need but places the "-" sign at the very left, in front of the string = -+++++2,100.31 instead of right in front of the number.

    Can somebody give a hand to make either way work please?

    Thanks in advance.
    David

  2. #2
    I just received the solution from a great guy elsewhere.
    Thought I should share it here;

    =REPT("+",15-LEN(TEXT(A1,"#,##0.00")))&TEXT(A1,"#,##0.00")

  3. #3
    This version should get the result you require.
    A2 is data to standardise formatted as Text
    B2 =SUBSTITUTE(A2,A2,REPT("+",15-LEN(A2))&A2)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •