Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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.


    Jon G
    GizmoCoder.Com

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Code:
        Sub Auto_Open()
            Cells.select
            Selection.Numberformat = xlNumber
        end sub


        Jon G
        GizmoCoder.Com

        Comment


        • #5
          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

          Comment


          • #6
            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.

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment


                • #9
                  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.

                  Comment

                  Working...
                  X