One of my pet peeves with Excel is how it will reformat (or try to reformat) data in cells based on what it thinks it should be. For example, if you save a file as csv with a date in the format of mmm-yy, the next time you open it the dates will become d-mmm, where the year was transposed to the day of the month. Drives me crazy, because the only way I’ve found to prevent it is to make sure that the dates are mmm-yyyy before I save the file again, which means a lot of repetitive editing when I’m normalizing a large number of reports.
I haven’t been working much with COUNTER book reports for assessment of ebook use until recently, when it seems we’ve started to tip towards increasing ebook use (and purchasing) at my library, so now I’m looking at them and ingesting them into my use assessment tool.
I tended to avoid the COUNTER book reports before because if I needed to edit the file, it was a hassle to get it to open in Excel without converting the ISBNs to display (and subsequently save) as 978E+12 if they didn’t contain dashes or something else to indicate to Excel that it should be treated as text and not a long integer. (Don’t get me started on the publishers who remove the dash in the ISSN, screwing up all the numbers that begin with 0 when opened in Excel.)
One way to deal with this is to select the column and choose Text to Columns in the Data tab. Click on through the menu until you get to the end where you can select the format as text. Miraculously, the full numbers display in the column (regardless of column size) and won’t save as 978E+12 if you hadn’t done that.
Alternatively, I’ve started opening the files in a text editor first, then finding and replacing all “978” with “978-“. This forces Excel to automatically treat the data as text instead of a long integer, and doesn’t need to be corrected every subsequent time the file is opened and edited.
How do you handle this?
