[Excel/SIMS] Interpreting Reg Groups as Scientific Formulae

[LosOjos]

Before I start writing a macro to correct this (which will be a total PITA as it'll have to be applied to every report in the system!), does anybody know how to stop Excel automatically formatting cells?

 

The problem I'm having is that some of our Reg groups contain the letter 'E' followed by a single number (e.g. 10E1, 9E2, 8E1 etc.). When you run a report from SIMS and set the output to Excel, Excel sees this and interprets it as a scientific forula so 10E1 becomes 100, 9E2 becomes 900 etc.

 

Any ideas?

[Anjelica]

Sorry LosOjos - I can't offer any help here. I spent the first 10 years of my school career dealing with this. (We are a 3 site school and from when the three sites amalgamated in the 80's the tutor groups were named 10W1 - meaning on the west site; 10E1 - meaning east site and 10C1 meaning centre site. When some wag decided we would start a house system I wrung my hands and worried for ages about having to make wholescale changes to Sims. When I had made the changes, I then realised I had lost this problem. Perhaps you could make this suggestion!!

[LosOjos]

It's so irritating! It stems from the way SIMS generates the CSV I believe. If each field (other than numeric fields) were encased in quotes, I think Excel would interpret them as text rather than formulae...

 

But nout I can do about that! So looks like I'm just going to have to live with it! Thanks for replying Anjelica, it's nice at least to know I'm not the only one!

[CESIL]

Have you tried creating an Excel template with the column in question formatted as text?

[LosOjos]

Have you tried creating an Excel template with the column in question formatted as text?

 

When SIMS runs reports in to Excel, the template you create isn't the same workbook that gets used for the result - that just imports the data from CSV, manipulates it and puts it in to a new sheet.

 

At the moment, my only way around it is to parse the column and "hard code" the values (just precede them with ', hard code sounded more impressive lol)