Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    format excel column (ExcelXP)

    HI

    Ive exported data from another system into an excel sheet.
    In my 1st column the data should be formatted as 3 digits. 3 digits. 3digits eg P08.001.259
    in some rows the data is formatted as numeric eg 0.584027777777778

    whats occured is from row 3 -25 its formatted with format I want eg P08.001.259
    then from row 26- 103 is the numeric format eg 0.584027777777778
    then from row 104- on wards its formatted with format I want eg P08.001.259
    & this keeps occuring.

    Ive had a look at the format of the column & the2 different cells & there appears to be no formula.
    Is there an easy way for me to apply the format Im wanting

    sample screen dump

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: format excel column (ExcelXP)

    HOw do you want the numbers to be displayed?
    eg what should the number:
    0.584027777777778

    Display in the cell?

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format excel column (ExcelXP)

    Adding to Steve's question: It is impossible to tell from a screen shot why the cells are displaying differently. Some of the cells could be text values and some of them could be numeric values, or they could all be numeric with special custom formats. It will probably save a lot of time solving your problem if you would upload a workbook that shows the problem. You could probably delete everything in the workbook except the column in question.
    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: format excel column (ExcelXP)

    I cannot open or download the attachment. I suspect its name was too long.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: format excel column (ExcelXP)

    P08.001.259

    I still do not understand. Do you want the cell with: "0.584027777777778" to display:

    "P00.584.028"
    Format - cells - number tab(custom):
    "P0"0.000"."000

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format excel column (ExcelXP)

    i wanted the data to display as P08.001.259

    attached sample of the file

    cheers diana

    edited: hi ive reattached the file with a shorter file name

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format excel column (ExcelXP)

    Hans - Ive reattached the file with a shorter filename.

    Steve - apologies if I have'nt been clear
    the cells displayed with 0.584027777777778
    i would like displayed with the format. "P00.000.000

    cheers
    diana

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format excel column (ExcelXP)

    Your cells that are displaying correctly have text values entered into them. The letter capital P followed by digits with period separators. Since those cells contain an alphabetic character, and two periods, it is all considered text. The cells which are not displaying the way you want contain numbers. Those values were entered as all numeric digits with only one period which Excel interprets as a number since the cells were formatted as "General". Your workbook is also corrupted since I am not able to format any of the cells. If I save the workbook as an HTML file, close Excel, start Excel and open the .HTML file, and save it back as a .xls file, the corruption appears to be removed and I can then format cells.
    Legare Coleman

Posting Permissions

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