Format Question

[SITCFanTN]

I feel silly asking this because I know it should be easy, but I just can't
figure this out. I have an Excel Spreadsheet and the data is in General
format and displays with one decimal and with the percent sign like this:
85.1% or 96.3%. I need to use this data in calculations so I need it to be a
number format as 85.1 or 96.3...with no percent sign. I can I accomplish
this. I have hundreds of rows of data that I need reformated in this way.
Thanks for your help.

 

[Dave Peterson]

85.1% is the same number as 0.851

If you really need 85.1 in your calculation, you can multiply that cell by 100
in your calculation:

=(a1*100)+b3
(for instance)

If you want to convert all those 85.1% percentages to 85.1 (no longer less than
1), you can:

Put 100 in an empty cell
Edit copy that cell
Select the range to fix
Edit|paste special|check multiply
Clean up that cell with 100 in it.

And format the fixed range to General (if it's still formatted as a percentage).

=====
If you need the percentage back for any calculation, you'll have to divide by
100:

=(a1/100)+b3
(for instance)

 

[Marcelo]

type 100 in one auxiliar cell, copy it and past special on the % column
multiplying. format as general

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"SITCFanTN" escreveu:

 

[JLatham]

Use the Format Painter? It's the little paint brush looking thing: in Excel
2003 and earlier, it's the icon in between the paste-from-clipboard and Undo
icons in the standard icon bar. In Excel 2007 it's on the [Home] tab, in the
Clipboard group; the bottom icon of the 3 at the left of the big Paste
dropdown.

 

[Joe User]

I have an Excel Spreadsheet and the data is
in General format and displays with one decimal
and with the percent sign like this: 85.1% or 96.3%.
I need to use this data in calculations so I need
it to be a number format as 85.1 or 96.3...with no
percent sign. I can I accomplish this.

How do you accomplish this manually?

Your description is subject to interprertation. Explaining your manual
process might help to resolve the ambiguities.

If you enter literally 10% into a cell that is formatted General, Excel will
automagically change the format to Percentage. The value in the cell is
actually 0.1. It can be used directly in other calculations; the appearance
of "%" has no adverse impact. In fact, this is the best form because the
calculation can be of the form A1*C1 instead of A1*C1/100, where C1 contains
the percentage.

If you are saying that the cell format is still General after entering the
value, and the value appears as "10%", I would guess that you actually have
the text string "10%". That may or may not cause computational difficulties
in other formulas, depending on those formulas.

I have hundreds of rows of data that I need reformated in this way.

Assuming the latter (cell with text that is formatted General), one way to
rectify this for "hundreds of rows of data" is:

1. Put the number 1 into some cell, and copy it.

2. Select the cells to be changed, right-click, and click Paste Special >
Multiply > OK.

3. While those cells are still selected, right-click, and click Format >
Number > Percentage.

That converts the numeric string into a number formatted as Percentage.

If the cells contents are already numbers formatted as Percentage, and you
wish them to scale them by 100 and remove "%" (not recommended), the
procedure is similar. In Step 1, use 100 instead of 1. In Step 3, click
Format > Number > Number or General instead of Percentage.

Those procedures are tentative, subject to your clarification of the
situation.


----- original message -----

 

[JLatham]

Sorry,
I thought you just had a format/display problem and had solved it. Didn't
realize you actually had a value problem.

Use the Format Painter? It's the little paint brush looking thing: in Excel
2003 and earlier, it's the icon in between the paste-from-clipboard and Undo
icons in the standard icon bar. In Excel 2007 it's on the [Home] tab, in the
Clipboard group; the bottom icon of the 3 at the left of the big Paste
dropdown.

I feel silly asking this because I know it should be easy, but I just can't
figure this out. I have an Excel Spreadsheet and the data is in General
format and displays with one decimal and with the percent sign like this:
85.1% or 96.3%. I need to use this data in calculations so I need it to be a
number format as 85.1 or 96.3...with no percent sign. I can I accomplish
this. I have hundreds of rows of data that I need reformated in this way.
Thanks for your help.