I am trying to format an Excel cell as per the example below. Is this possible?
$ 0.00 $ 24.20 $ 0.00
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
If format required is of the form "$ 1258.47" then :
Heading in First Row : Column 1 : "Amount" Col. 2 : "Formatted Amount"
Enter all values in A2, A3, A4 etc.
We use Concatenate Formula to get the result. In formula we can also use "&" to Concatenate.
So in Cell B2 Enter the formula : "$ "&A2 Copy the formula in B2, down the column to get desire format.
To right align the format to see the unit digit under unit digit etc..
Enter Further Headings in First Row as :
C1="Lenth of Amount" : D1="Spaces Required" : E1="Pedding String"
F1="String Size" ; So suppose we need size of 18 then enter 17 in Cell F2 Because One for "$" is subtracted. THis will remain Constant and can be changed at any time by entering different value in Cell F2.
Now Enter Formulas as shown below :
In C2 Enter Formula : =LEN(A2) In D2 Enter Formula : =$F$2 - C2 ( Absolute reference of the Cell ) In E2 Enter Formula : =REPT(" ",D2) ( To repeat the spaces required )
Finally, Now in B2 enter the Formula : ="$"&E2&A2
Now copy the Formula of B2 down the column B to get the desired result.
I hope you are satisfied.
If not, or for any query please revert back.