Forum Discussion
Number format within a text formula
Hello,
I'm trying to put numbers from cell references into a formula, which itself is simple, but how do I format those numbers once there?
i.e. I would like to say '±1% tolerance for 100°C = ±1.00°C'
but I get '±0.01 tolerance for 100°C = ±1°C
I would always like to show 2 decimal places, even if it is .00
I would also like to show the ±0.01 as 1%
="±"&B3&" tolerance of "&A3&"°C" &" = ±"&A3*B3&"°C"
John
As mentioned by Mr Lewin...
To achieve the desired formatting within your text formula, you can use the TEXT function to format the numbers before concatenating them into the string.
Here is how you can modify your formula:
="±" & TEXT(B3, "0.00%") & " tolerance for " & A3 & "°C = ±" & TEXT(A3*B3, "0.00") & "°C"
In this modified formula:
- TEXT(B3, "0.00%") formats the number in cell B3 as a percentage with two decimal places.
- TEXT(A3*B3, "0.00") formats the product of cells A3 and B3 as a number with two decimal places.
This will ensure that the numbers are formatted as desired within the text formula. So, for example, if B3 contains 0.01 and A3 contains 100, the result will be "±1.00% tolerance for 100°C = ±1.00°C". The text was created with the help of AI.
File with the example is included.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
As mentioned by Mr Lewin...
To achieve the desired formatting within your text formula, you can use the TEXT function to format the numbers before concatenating them into the string.
Here is how you can modify your formula:
="±" & TEXT(B3, "0.00%") & " tolerance for " & A3 & "°C = ±" & TEXT(A3*B3, "0.00") & "°C"
In this modified formula:
- TEXT(B3, "0.00%") formats the number in cell B3 as a percentage with two decimal places.
- TEXT(A3*B3, "0.00") formats the product of cells A3 and B3 as a number with two decimal places.
This will ensure that the numbers are formatted as desired within the text formula. So, for example, if B3 contains 0.01 and A3 contains 100, the result will be "±1.00% tolerance for 100°C = ±1.00°C". The text was created with the help of AI.
File with the example is included.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Thank you! This is perfect 😀
