# number format in toad

hi
sometimes i would like to export the result of my query from toad to ms excell
there , i have to every time to format the numbers , where always coming as currency format
i used those 2 formulas :
`````` to_char(cur_sls,'999,999,999') cur_sls
``````
as number with no decimal places
and
``````to_char(100 * (PRV_SLS-PRV_EXP)/NULLIF(PRV_SLS,0) ,'fm9990.00')||'%'
``````
to forrmat number as percentage with 2 dicimal places
it is ok in the result itself , but when exported to toad , i found that i need to again convert the strings to number
any way to direct convert as number
Asked:
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
to_char converts your results to text

try round(x,2)  to convert your numerical value to 2 decimal places while still keeping it a number

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
and what about the percentage with 2 decimal places
Commented:
that's what the "2" is for in the round

if you append the '%' at the end, then you're turning the value into text again.

If you want % to show up in the results, but still be numeric, then let Excel do the formatting for you
Author Commented:
yes correct
but no way to format the number as : 999,999,999 as number instead of text ?
Commented:
no,  commas aren't numeric, commas are text

like percentage, if you want non-numeric characters in the representation and it should still be considered numeric, then let Excel do the formatting for you
Author Commented:
ok , finally , and i don't know if this regards to toad or to excell it self
when i export to excell , and select format as comma , it becomes accounting format with currency symbol
any way to change it to instead of another step in excell
Commented:
you're exporting text and excel is doing its best to interpret it.

I would export numbers only, and put all formatting on the excel side.
Author Commented:
No , I changed to number , then when I format in excell it comes like this
Author Commented:
i still have a problem with decimal
`````` (CASE WHEN grouping_level IN(1,3) THEN round(100 * CUR_SLS-CUR_EXP,2) ELSE NULL END) AS CUR_PROF,
``````

in the data view of toad it displayed as 2.65
when exported to excell it comes  3
then i tried to formatted with comma in the toolbar of excell it comes 3 \$
then should format cell to select no currency sympol with 2 decimal places to show again 2.65
should this posted as "excell" topic also ?
Commented:
I am a topic advisor, I can add the excel topic if you want.

Did you set your numeric format to display 2 decimal places?

Author Commented:
yes , when doing formatting like this it is ok
but i want to save this step , because my query has alot of columns
and each column will have different format
in conclusion , i don't want to use formats of excel
Commented:
>>>in conclusion , i don't want to use formats of excel

then you can't do what you want

Neither Toad nor Oracle has any control over how Excel will format the data.
Author Commented:
When I'm copying data from any source to excell , the numbers stay general number format , except toad , when I copied from it to excell it coming with current format
Commented:
>>> copying data from any source to excell , the numbers stay general number format

do that then.

copy to excel instead of creating a new file.

Don't export as "Excel File"  instead start Excel and then choose export as "Excel Instance"

When I do File, 2.65 it is formatted as 3,  when I export to an Instance it is formated as 2.65
Commented:
one last thing,  are you choosing the "general format"  when you export?
When I do that, the excel file works,  if it doesn't for you, then try the Instance method.

or, you may have to contact Dell and put in a request for a new Toad feature

Author Commented:
I'm not sure I have this option , I will check it.  It may work , or , last question : any setting in the registry of oracle such as date format , is there any setting of numbers there ?
Commented:
Those settings apply to your NLS_NUMERIC_CHARACTERS setting which controls your grouping and decimal characters.  (In the US  commas between thousands and millions and a period to indicate the decimal point.)

Plus, even if there was a formatting setting, that would mean the your numbers would be converted to text in order to satisfy the format.  So you wouldn't want to use such a thing even if it did exist.
Author Commented:
So I will check that option in my toad
Commented:
I'm using 12.7.1.11.

I don't know what version added the "general" option.
I think it's been there for a while but I honestly don't use it much so I could be wrong.
Author Commented:
the option is there , but still same issue
i think excell option , if you don't mind , could you added to excell topic
by the way , i just see the report section of toad , it seemed wonderfull tools
i want to learn about it
it will save a lot of time of exporting to excell
how i can learn about it ?
Commented:
it's unlikely an Excel person can tell you how to get toad to send data differently.

If toad created the file, then excel can't do anyting because toad is in control.

Once the data is in toad, you already know how to format it.

If you use the export to excel instance option you might have some options with vba that would detect the new worksheet being added and format the data for you.

But, both the general option on file create and export to instance work for me.
If toad isn't doing what you want you will probably need to talk to Dell.
The toad staff is very responsive.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.