Align text string to the right

[Jing]

I have the following Formula:
L10 = =IFERROR(IF(VLOOKUP(D10,'Sales Stats Comparison'!$D$10:$M$67,10,FALSE)<100%),"%TT Not Met"&" "&ROUND((K10-I10,4)*100&"%",IF(C10="",IFERROR((K10-I10),""),"")),"")

When this returns: (Example)
%TT Not Met 59.99%

I would like to try to have is the "%TT Not Met" aligned Left & the 59.99% aligned right.

 

[LxQ]

Hopefully someone can come up with a better option... if you know the width of your cell, maybe you can add some spaces as buffer

=IFERROR(IF(VLOOKUP(D10,'Sales Stats Comparison'!$D$10:$M$67,10,FALSE)<100%),"%TT Not Met"&right(" "&ROUND((K10-I10,4)*100&"%",30),IF(C10="",IFERROR((K10-I10),""),"")),"")

 

[shg]

I'm not wading into that formula, but A3 below is aligned as you describe, though it doesn't appear that way here:

	A​	B​
1​	%TT Not Met	A1: Input
2​	0.59​	A2: Input
3​	%TT*Not*Met 59%	A3: =SUBSTITUTE(A1, " ", CHAR(160)) & TEXT(A2, " 0%")

The formula in A3 replaces the spaces in A1 with non-breaking spaces (what appears above as asterisks, but they look like spaces in Excel), and catenates that with the number in A2 formatted as a percentage with a leading space. Cell A3 is formatted with horizontal alignment set to Distributed (Indent), which stretches that space across the unused width of the cell.

 

[pgc01]

Hi

Your formula does not work.

Maybe you can explain how it works and the different results you can get from it, depending on the results of the If's conditions?

 

[Jing]

The formula is trying to generate some sort of Efficiency rating.

if say a rep has not reached their Target Vlookup(......)<100%
it will show "%TT Not Met" & shows their efficiency rating to the right within the same cell.
otherwise it will just show the rating within the cell.

this is really an esthetics thing as %TT Not Met is lined up on the left side of the cell. and the % rating is not lined up on the right.

words align left
% values align Right

%TT Not Met 59.99%

%TT Not Met 27.41%

-16.32%

4.95%

%TT Not Met 23.49%

%TT Not Met 5.23%

<tbody>
</tbody>

really this is just to line it up to make it easier to read.

 

[pgc01]

I see.

I've used custom formatting in the past for a similar problem. In this case, since you have a If(), you'd have to use conditional formatting.

You just have to modify the formula to return just the value and define a format with the with the text literal to the left and the number to the right (using the *)

Unfortunately I've just tried to paste the formula you posted into a worksheet and it has syntax errors, there is something wrong with it, and so I cannot post an example using it. (maybe some parentheses out of place?)



If you want to try this solution and have difficulties implementing it, post a formula that works so that we can post a solution.

 

[Jing]

I see.

I've used custom formatting in the past for a similar problem. In this case, since you have a If(), you'd have to use conditional formatting.

You just have to modify the formula to return just the value and define a format with the with the text literal to the left and the number to the right (using the *)

Unfortunately I've just tried to paste the formula you posted into a worksheet and it has syntax errors, there is something wrong with it, and so I cannot post an example using it. (maybe some parentheses out of place?)



If you want to try this solution and have difficulties implementing it, post a formula that works so that we can post a solution.

Sorry here is the updated formula.
=IFERROR(IF(VLOOKUP(D10,'Sales Stats Comparison'!$D$10:$M$67,10,FALSE)<100%,"%TT Not Met"&" "&ROUND(K10-I10,4)*100&"%",IF(C10="",IFERROR((K10-I10),""),"")),"")

I am not 100% sure of what you mean.

Thank you for your help so far.

 

[pgc01]

OK

You said that this formula is in L10. That is how I tested

1 - First change the formula to return just the number value, like:

=IFERROR(IF(VLOOKUP(D10,'Sales Stats Comparison'!$D$10:$M$67,10,FALSE)<100%,ROUND(K10-I10,4),IF(C10="",IFERROR((K10-I10),""),"")),"")

, and use percentage number format

This is your formula, just not adding the text part.

2 - you only want the text part if the VLookup() is less than 100%

Add that a conditional formatting condition:

=VLOOKUP(D10,'Sales Stats Comparison'!$D$10:$M$67,10,FALSE)<100%

and for that condition use the number format:

"%TT Not Met"* 0.00%

This will align the text part to the left and the number part to the right.
This is done by the "* " in the format, that will fill the space between left and right. Notice that you can make the cell wider with no problem, the text will adjust.


Remark: maybe your formula can be simplified. I did not get into that.

 

[Jing]

Man, That's awesome!!!

Thank you so much.

 

[pgc01]

You're welcome. Thanks for the feedback.