I needed the results of my calculations in excel look "pretty." Normally, good old Ctrl+1 and format the cells according would have suffice. However, in this case, I have the additional requirement of combining multiple results. Specifically, I needed to generate an legible exam solutions sheet. For example, I would want to have
The answer for (a) is $0.53 and (b) is 22.22%
where the two numbers are generated by formulas.
The quick answer is to use the TEXT command to specific the formatting and have the output as text.
For example, if the formula generating the answers are in cells A1 and A2, trying to combine the outputs using the following formula
="The answer for (a) is $"&A1&" and (b) is "&a2&"%"
would result in the combined output of
The answer for (a) is $0.534883720930233 and (b) is 0.222222222222222% .
To get around the problem, the cells needs to be converted to text. The TEXT command does this and allows the user to specific the formatting. Thus, the following formula
="The answer for a) is "&TEXT(A1,"$0.00")&" and b) is "&TEXT(A2,"0.00%")
would result in the combined output of
The answer for a) is $0.53 and b) is 22.22% .
If leading zeros are needed, the following formula
="The answer for a) is "&TEXT(A1,"$0000.00")&" and b) is "&TEXT(A2,"0.00%")
would give you
The answer for a) is $0000.53 and b) is 22.22% .