remove percent sign

[sd101]

Hi all -
How do I remove the percent sign from % formatted cells without altering the formulas? I feel this should be possible through the format dialog, with a custom "format code" of some sort -- thanks for your help!

sd

 

[Reid]

From the Edit menu:

Clear, Formats

 

[sd101]

Thanks, but I want to remove the percent sign, not also change the value in the cell. To reiterate,

before
1.20%

after
1.20

this is a calculated cell, and the formula must be the same before and after (so I can't just multiply by 100)

 

[shippey121]

what formula are you using to get the 1.2% ??

 

[Barry Katcher]

Custom number format: #.00

Book1
	A	B	C	D
1	200	240	1.20
Sheet1

 

[sd101]

I had no idea this would be so difficult -- apologies. Here is the setup:

The formula is a combination of other cells, and returns the numerical value of:

0.012

When I format it as a percent, the actual numerical _value_ in the cell (which is referenced later on in the sheet) remains 0.012, but on the screen and when printed, it appears as:
1.20%.

What I need:
It should retain the value 0.012 (so it doesn't mess things up later on) but when printed, it should look like:
1.20

(the problem is I've got a whole bunch of these, and the % signs clutter the printout, making my boss unhappy...)


Thanks everybody!

 

[Oaktree]

You have kinda strangled your options because of your

this is a calculated cell, and the formula must be the same before and after (so I can't just multiply by 100)

comment. You cannot format a cell to display as a % without a % sign. You have to change the value, which means adjusting your formula to include a *100 on the end (or similar adjustment).

 

[Ron Morris]

As Oaktree states, you must remove the percentage formatting to get the result you want. The formula will still give you the percentage when adding *100 at the end of your formula.

For example:

If you want to show two decimal places, =ROUNDUP((B1/A1)*100,2)

Ron

 

[Barry Katcher]

Ooops - I misread your original post. Try the method below.
Formula in D1 is: =TEXT(C1*100,"#.00")

Book1
	A	B	C	D
1	20,000	240	1.20%	1.20
2			Hide this	Show
3			column.	this
4			It can still	column
5			be used for
6			calculation.
Sheet1

 

[pgc01]

Hi sd

I had that exact problem in another context. Since I did not find an excel solution I used a windows solution.

I simply edited the font I was using, replaced the % sign with nothing and saved it with another name.

Then I just had to select that font for the cells that used the format with the percent sign. Excel thinks that the % is being displayed but, in fact, it's not because the new font displays the % as nothing.

Notice that this is only to be used as a last resource solution. If you'd want to work with the workbook in another computer you'll have to install the new font.

Hope this helps
PGC