Use TEXT function but keep contents as a VALUE

[comoke1024]

Good afternoon all,

I'm not sure if what I'm trying to do is possible, but I'm having trouble with this problem:

I have a percent value in Cell A1, ex: 50.176%
I want to enter a number in B1 that will control how many decimal places that A1 contains AND displays.
I can enter into cell C1 the FIXED function, FIXED(A1,B1) to force it to use the number of decimal places, let's say 4 to show 50.18%, but it displays as a decimal, 0.5018.
I know I can use the TEXT function to turn it back to a percent, TEXT(C1,"##.###%"), but this changes it to a text. I need to be able to copy-paste this to another spreadsheet and keep it formatted as a percent (it displays as a percent correctly, but in the new sheet it's stored as a text).

Is this possible? If so, is it possible to do without Macros? I'm doing this for Excel users of all skills, so I'd like to stick to formulas if possible. And I'd like to avoid having to adjust the formatting manually each time I copy-paste.

Thank you in advance for your help!

 

[DRSteele]

Welcome to the forum.

Try the following. A1 is the number of interest (formatted as a decimal); B1 is the number of decimal places you want; C1 is the text output; and D1 is a number (because the maths operation converts it to a number).

If you try to do maths operations on text that contains numbers, Excel will automatically convert it to a number in that operation.

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]50.176%[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #E2EFDA"]50.18%[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1.5018[/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=FIXED(A1,B1)*100&"%"[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=1+C1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]