Use TEXT function but keep contents as a VALUE

comoke1024

New Member
Joined
Jan 13, 2017
Messages
1
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!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
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.

ABCD
150.176%450.18%1.5018

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet13

Worksheet Formulas
CellFormula
C1=FIXED(A1,B1)*100&"%"
D1=1+C1

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top