# Align text string to the right

#### Jing

##### Active Member
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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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),""),"")),"")

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.

Last edited:
Hi

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?

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.

Last edited:
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.

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.

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

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.

Man, That's awesome!!!

Thank you so much.

You're welcome. Thanks for the feedback.

Replies
6
Views
713
Replies
1
Views
198
Replies
1
Views
101
Replies
0
Views
270
Replies
16
Views
450

1,196,102
Messages
6,013,476
Members
441,767
Latest member
Craigh4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back