Excel Formating

[tiger19]

Capture.PNG

Book1.xlsx

Hi im extracting data from sql. the columns are converted to nvarchar. how can i freely format it in excel? to percentage or to number with comma. is not working.

[alansidman]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[tiger19]

Hi i have already attached in my post. The output im getting from getdata-->sql. statement as below:

select convert(nvarchar,[total])[total],convert(nvarchar,[volume])[volume] from ... where ...
union all
select ''[total],''[volume]

I couldnt change the format in excel.

[huuthang_bd]

Your data is text, not number. To convert the number formatted as text to real number you can copy a blank cell and pastespecial - add into range containing number formatted as text.
With your spreadsheet, You can follow step by step the instruction below:

1. Copy A8
2. Select A2:B7
3. Right click and select Paste Special...
4. On Paste Special windows, Choose Add (Operation group) and hit OK.

[tiger19]

data will be refresh daily, everytime i need to copy and paste? is there any way i can just change the format without copy and paste it.

[huuthang_bd]

If the export data is text, you have to do something to convert it to real number.
The sql export data to new spreadsheet or a specific spreadsheet?

[tiger19]

It will export into specific spreadsheet, upon click refreshed will overwrite the same spreadsheet. I was thinking user may play around with the formating in excel. So there's no way I can do that? Any advice. Thanks.

[AliGW]

Administrative Note to All Participants:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[huuthang_bd]

VBA can automatically fix data for you. Put below code into the modules of sheets where sql export data.

Please Login or Register  to view this content.

[tiger19]

can you guide how can I apply that? step by step link will be appreciate. Im new in VBA.

[huuthang_bd]

Please follow step by step below instruction

1. Open the file that sql will export data.
2. Right click on tab name where sql will export data and hit 'View code', the window to write VBA code will appear.
3. Copy that code and paste into this.
4. Press Alt + Q to close VBA window and return Excel.
5. Repeat step 2 to 4 for each tab you want to apply.
6. Save as your file as type '.xlsm'.

[tiger19]

thanks. is working fine now.