Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

How do I tell Excell 2007 not to decide how it is going to format cell contents

  • 12-08-2010 05:00PM
    #1
    FSL
    Registered Users, Registered Users 2 Posts: 1,456 ✭✭✭


    I have an Excel document which contains data from another system. One column is a reference many of which are in the form nnnnn-nnnnn. If the first part of the reference is between 1 and 12 and the second part is between 1 and 31 Excel insists on showing it as a date. i.e. 1-18 displays as January 18 but 13-18 or 1-32 would display as typed. No matter how I format the cells Excell just ignores it.

    I also had a similar problem where I extracted some information from a series of files. One of the items was the scale used, in the form 1:nnnnn which only displayed correctly if nnnn was greater than 9999. As I was generating the data I got around it by putting a space as the first character, Excel then displayed the data correctly.

    I have no control over the reference data as that is generated by from proprietary software over which I have no control.


Comments

  • #2
    Capt'n Midnight
    Moderators, Recreation & Hobbies Moderators, Science, Health & Environment Moderators, Technology & Internet Moderators Posts: 96,078 Mod ✭✭✭✭Capt'n Midnight


    format the cells as text


  • #3
    homer911
    Registered Users, Registered Users 2 Posts: 5,067 ✭✭✭homer911


    Something sounds strange - is your source file really Excel or is it a CSV file?

    If the file is CSV, then you have some hope of controlling how Excel handles it by editing it in a text editor before opening in Excel

    If the data really is in Excel, then using a text format on a value that is already displayed as a date, will only display it as a number in text format. Its only if the cell is formatted as text before the value is entered that it will be displayed as desired


  • #4
    FSL
    Registered Users, Registered Users 2 Posts: 1,456 ✭✭✭FSL


    It is in Excel output from a Microsoft accounting package. I already knew it would display correctly if the cell was pre formatted. I cannot however pre format a spreadsheet generated by another system.

    As it happens the result set is displayed before being exported so yes I can select all, copy, open a spread sheet, format the appropriate column(s) and paste the data. However your average used does not want to do that when there is an export button.


Advertisement