Custom Formats (Removing the percentage symbol)

[JimbobmiJ]

Hi Guys,

I'm trying to set up custom formats in the personal.xls. I have already attached a macro to a button to format a number to display positive numbers by thousandths and place brackets - in red - to display negatives.

The other format I want to setup is the same as the above but with percentages - display a positive percentage without the percentage symbol and display negative numbers in brackets without the percentage symbol.

I want to be able to use this on different reports (hence macro to the personal workbook).

I have scanned the posts and cannot see if the percentage issue has been solved.

Is there a VBA solution to this?

Thanks,

 

[Richard Schollar]

Hello Jim

There is a kinda way:

With a few percentages in eg A1:A10 (ie numeric values like 1.5, 0.5, 0.2 etc) go Format>Cells. On the Alignment Tab, check Wrap Text (this is important). Back on the Number tab go to Custom and type in:

0.00'Ctrl+j'%

now, the 'Ctrl+j' bit means hit the j button whilst holding down Ctrl - this will insert a newline character (which will look like a little box). Click OK.

On another point: you may find it useful to define these formats as Styles and save them down in a workbook with the name Book.xlt and place this in your XLSTART folder - this will then serve as the default new workbook format for all new workbooks and will already contain the style you have just saved.

 

[JimbobmiJ]

Hi Richard,

Thanks for the great info. As it is only an aesthetic format, this should work fine.

I can even get this into a macro and have a button on my toolbar for easy access.

You're a champ!

Cheers,

James

 

[daviss31]

I followed the directions to remove the percentage sign in custom number formats. However, when I try to view the sheet in print preview and then subsequently print it, I only see a percentage sign in the cells that I formatted. The numbers are on the sheet on the screen but not when I print.

Also, when I further format the cells with underlines, the percentage symbol appears with no numbers in the formatted cells.

Please let me know what I am doing wrong.

Thanks!

 

[chrisseanhayes]

multiply the number by 100 and just format it as a number.

 

[Derman]

This took me two hours to solve (MS could have fixed the problem long ago).
TO REMOVE THE % SIGN FROM A COLUMN, ROW, OR BLOCK OF NUMBERS:
1) Highlight the %numbers, then change their format to General (Format-Cells-General).
2) In a separate cell on the same spreadsheet, type 0.01 formatted as a Number
3) Highlight and copy (Ctrl-C) the contents of that one cell
4) Re-highlight the row, column, or entire block of numbers to be rewritten without percent signs
5) Click on Edit-Paste Special-DIVIDE (NOT multiply)
6) When you hit Enter, all your highlighted numbers will be changed to the proper VALUE without the percent SIGN.

 

[kkmatheso]

multiply the number by 100 and just format it as a number.

What do you do in the case of 100% (A1/A2)*100 which gives you a zero error (#DIV/0!)

I have it working with the above referenced formula, but when the value is 0 (ie. 10/10) then I get a zero error instead of 100% I need to show the cell value as a percent, but without the percentage sign...HELP! I have it working with the above referenced formula, but when the value is 0 (ie. 10/10) then I get a zero error instead of 100%

 

[vbutler]

Hello all, I'd like to do a custom format to format as a percentage but without the % sign. In other words, multiply the value by 100. But I don't want to have to have yet another formula; I'd prefer this to be a custom format.

For example, In cell where value is .345, I want it to be custom formatted to 34.5.

I tried to follow suggestions above, but I'm not getting it. Is there a way to do this? Thanks in advance!

 

[jclubbe]

Enable Text Wrapping to all applicable cells.
In Custom Format, type
#.##(Ctrl+J)%
for (Ctrl+J) Hold in the Ctrl key and hit J - this will jump to the next line
This will force the % sign to then next line. You may run into problems if you have some rows displaying more than a single line, as the % sign will show underneath.
You can then try this Custom Format:
#.##(Ctrl+J)" "(Ctrl+J)" "(Ctrl+J)%
Add as many (Ctrl+J)" " as required to eliminate showing the % sign.

 

[Sleepyquark]

This took me two hours to solve (MS could have fixed the problem long ago).
TO REMOVE THE % SIGN FROM A COLUMN, ROW, OR BLOCK OF NUMBERS:
1) Highlight the %numbers, then change their format to General (Format-Cells-General).
2) In a separate cell on the same spreadsheet, type 0.01 formatted as a Number
3) Highlight and copy (Ctrl-C) the contents of that one cell
4) Re-highlight the row, column, or entire block of numbers to be rewritten without percent signs
5) Click on Edit-Paste Special-DIVIDE (NOT multiply)
6) When you hit Enter, all your highlighted numbers will be changed to the proper VALUE without the percent SIGN.

This took me two hours to solve (MS could have fixed the problem long ago).
TO REMOVE THE % SIGN FROM A COLUMN, ROW, OR BLOCK OF NUMBERS:
1) Highlight the %numbers, then change their format to General (Format-Cells-General).
2) In a separate cell on the same spreadsheet, type 0.01 formatted as a Number
3) Highlight and copy (Ctrl-C) the contents of that one cell
4) Re-highlight the row, column, or entire block of numbers to be rewritten without percent signs
5) Click on Edit-Paste Special-DIVIDE (NOT multiply)
6) When you hit Enter, all your highlighted numbers will be changed to the proper VALUE without the percent SIGN.

Thank you so much for sharing it! I think this is the simplest solution and it also works in Excel 2016.