DavidTyler
New member
- Joined
- Nov 13, 2014
- Messages
- 2
- Reaction score
- 0
- Points
- 0
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
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