Leading characters - Negative number and comma problem

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 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")
 
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)
 
Back
Top