Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: How can I stop Excel from formatting my numbers on its own?

  1. #1
    Join Date
    5th July 2004
    Location
    Redditch, UK
    Posts
    4

    How can I stop Excel from formatting my numbers on its own?

    My problem is as follows;

    I have a CSV file and one of the fields contains numbers such as mobile telephone numbers; when I open the csv file in excel to edit it the numbers are represented thus; 447800707414 represented as 4.47801E+11.

    Is there a way I can turn off this feature in excel which automatically reformats the number? I cannot format the columns as it is a CSV file and only stores the data, not the format information.

    Many thanks for any advice given.
    Seb Spiers

  2. #2
    Join Date
    11th June 2004
    Location
    Canada
    Posts
    153
    Format the cell as "Number" instead of General

    If you select the cells with the problem, click on Format > Cells

    In the "Number" tab, select NUMBER FORMAT


    This should format all the numbers correctly for you.

  3. #3
    Join Date
    5th July 2004
    Location
    Redditch, UK
    Posts
    4
    Quote Originally Posted by Jong
    Format the cell as "Number" instead of General

    If you select the cells with the problem, click on Format > Cells

    In the "Number" tab, select NUMBER FORMAT

    This should format all the numbers correctly for you.
    Jong,

    I cannot format the cells as it is a csv file, when the file is saved the formatting will be lost. I need a way of disabling the feature in excel by default for csv files or even all files.

    Thanks anyway.
    Seb Spiers

  4. #4
    Join Date
    11th June 2004
    Location
    Canada
    Posts
    153
    If you are allowed to change the formatting, you could run a script that will automatically format it for you whenever you open the workbook.

    VB:
    Sub Auto_Open() 
        Cells.select 
        Selection.Numberformat = xlNumber 
    End Sub 
    
    

  5. #5
    Join Date
    5th July 2004
    Location
    Redditch, UK
    Posts
    4
    Quote Originally Posted by Jong
    If you are allowed to change the formatting, you could run a script that will automatically format it for you whenever you open the workbook.
    I am not able to apply any kind of formatting as the file is a CSV (comnma seperated value) file, this means I am only able to save the data in a basic format.

    I need to disable the feature in excel which formats the numbers automatically.
    Seb Spiers

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716
    Hi Seb

    Open a blank Excel Workbook and go to Data>Import external data>Import data

    At the last step of the Text import wizard set the desired formatting.

  7. #7
    Join Date
    5th July 2004
    Location
    Redditch, UK
    Posts
    4
    Quote Originally Posted by Dave Hawley
    Open a blank Excel Workbook and go to Data>Import external data>Import data

    At the last step of the Text import wizard set the desired formatting.
    I need to retain the csv file format. I cannot apply any kind of formatting. Is there a way I can stop excel from formatting numbers in this way without applying any kind of formatting to my csv file.
    Seb Spiers

  8. #8
    Join Date
    26th January 2003
    Location
    Indiana, USA
    Posts
    784
    Seb,

    If you wish Excel not to recognize your data as a number, might I suggest you change your data so that it is recognized as a string, possibly by inserting the appropriate parentheses and dashes to your phone numbers and re-saving the file.


    Rich D

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    8th August 2003
    Location
    UK
    Posts
    61

    Import Wizard text setting then re-format after ....

    Seb,

    This happens to me when I extract data from other databases on my server that can only be EXPORTED as *.txt, *.csv, *.dat etc.

    BUT, YOU CANNOT RETAIN THE CSV FORMAT IN EXCEL, IF YOU TRY TO YOU WILL LOOSE ALL OF THE FUNCTIONS YOU WANT TO USE BY IMPORTING THE DATA INTO EXCEL IN THE FIRST PLACE ???

    Tip : What I always do, is IMPORT the DATA, and you will soon notice that this OPEN's a single WORKSHEET spreadheet, SAVE this before you do anything as 'newfile.csv'. Then SAVE AS 'newfile.xls'. Therefore you have two identical files one with which you can reference your original data and one that you can play around with in EXCEL.

    [This means you will end up with 3 files; 'originaldata.csv', 'newfile.csv', and 'newfile.xls', seems a bit much but its very effective. Note 'originaldata.csv' and 'newfile.csv' are identical in essence, apart from 'newfile.csv' was opened in EXCEL and 'originaldata.csv' was EXPORTED from your original source - confused???? Stay with me!

    Try this,

    Step 1: Save the ORIGINAL DATA file as a 'originaldata.csv' as you have stated, and then,

    Step 2: Try and OPEN this file from the FILE menu in EXCEL,

    This should automatically open the TEXT IMPORT WIZARD, (Yes this is TEXT, but stay with me).

    Step 3: Page 1 of 3 of the IMPORT wizard, select 'Delimited', then 'Next'.

    Step 4: Page 2 of 3 of the IMPORT wizard, select 'Comma', then 'Next'.

    Step 5: Page 3 of 3 of the IMPORT wizard, (THE IMPORTANT BIT), click on each column in turn and determine the format, or even skip it alltogether. ENSURE YOU SELECT TEXT for the column with lots of numbers and zeroes, to avoid it formatting as SCIENTIFIC then select 'Finish'.

    Step 6: As my tip suggests, save this file straight away as a 'newfile.csv', even though you know that EXCEL thinks your NUMBER column is TEXT, it doesn't matter anyway.

    Step 7: Then SAVE AS 'newfile.xls'. (you know have 3 comparatively identical files).

    Step 8: In 'newfile.xls', revert the TEXT back to NUMBER, but what I have noticed you sometimes have to do is COPY and PASTE all of the TEXT COLUMN into a NEW column already formatted as NUMBER, as the TEXT format seems to stay with the existing data, god only knows why. INSERT a column, format it as NUMBER and COPY and PASTE - its the only sane way !!!

    Step 9 Delete the redundant column.

    I am not sure on what you want to do next..... thats up to you, but lets say you want to change the data and EXPORT it back and therefore modify your 'originaldata.csv' file............. Mmmmmm ?????

    Sticky one this, I reckon you'll end up in a right mess, unless you are aboslutely spot on with your work in EXCEL, in fact I DEFINATLEY DO NOT RECOMMEND MANIPULATING A DATA FILE USED BY A DATABASE, AS ATTEMPTS TO OPEN THIS LATER USING FORMS IN THAT DATABASE COULD CRASH IT, IF THE FORMATS HAVE BEEN TAMPERED WITH AND THE DATABASE HAS LOTS OF EMBEDDED FIELD VALIDATION - WHICH MOST OF THEM DO THESE DAYS TO AVOID DATA ENTRY ERRORS, etc etc.

    Good luck have fun !

    Simon.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Stop/Prevent Web Query Auto Formatting
    By mm_sl in forum EXCEL HELP
    Replies: 11
    Last Post: April 24th, 2009, 16:56
  2. Stop Large Numbers Shwoing Scientific Notation
    By lexis200 in forum EXCEL HELP
    Replies: 2
    Last Post: August 17th, 2007, 05:17
  3. Stop Time Formatting
    By telymanbws in forum EXCEL HELP
    Replies: 3
    Last Post: March 16th, 2007, 17:26
  4. Stop Scientific Format For Large Numbers
    By dodger7 in forum EXCEL HELP
    Replies: 13
    Last Post: November 22nd, 2006, 16:10
  5. Writing macros to stop minus numbers
    By Maggie-McGill in forum EXCEL HELP
    Replies: 2
    Last Post: March 16th, 2006, 02:43

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno