OK, this is kinda weird and I need help. I work doing MALL security, and i'm trying to make an excel list of all of our banned persons. Part of this list includes police report numbers, and the way our local PD does report numbers is the first 2 digits of the year (07) a dash (-) and then a number that increases thoughout the year (1-9999+), so a typical report number towards the end of the year will look like 07-12345, excel has no problem typing these in. The problem is the numbers at the begining of the year is one number short (07-1234, notice the lack of "5").

My problem is that with the 4 spaced report numbers excel automatically converts them to a date. The number i'm on is "07-3196" and it is converting it to "Jul-96". Keep in mind that I also have to search for these numbers to. Is there a formula, or anyway to keep excel from automatically converting these numbers to date and time? Any ANSWERS would be appreciated. (please execuse any spelling or grammar, long day, LOL)It's been 8 years since I had to do something just the same. There were ways in the '90's version to turn off the smart parts of excell.

First, Ive found descent help at the local public library. Whether it is from the guy in charge who was familiar with Office products or more importantly, there is a full section of donated computer books for DUMMIES.

Having said that, if your controlling the excell template, simply add a symbol in front of the 07 for example; #, *, BP07 [for Banned Persons], or anything you choose. It will fool the program and not recognise the data as an industrial form of writing a date as it is trying to auto correct.

As a last note, if you are adding a symbol, make sure in your footnotes and job task description, you add the change. This will help if someone else is putting in fresh data during your vacation abscense.

I hope the suggestions help.Quote from: Northpike on November 04, 2007, 08:02:50 PM

My problem is that with the 4 spaced report numbers excel automatically converts them to a date. The number i'm on is "07-3196" and it is converting it to "Jul-96". Keep in mind that I also have to search for these numbers to. Is there a formula, or anyway to keep excel from automatically converting these numbers to date and time? Any answers would be appreciated. (please execuse any spelling or grammar, long day, lol)