Tech Support banner

Status
Not open for further replies.
1 - 3 of 3 Posts

·
Registered
Joined
·
2 Posts
Discussion Starter #1
Hello! I stumbled upon this place in my frantic Google searching, so I hope you can help me (and I'm not committing too many faux pas!).

I've got an Excel workbook that creates case number labels for the lab I work at. It's two sheets, with the first being a nice-looking interface with fields you can enter information into to determine what prefix to add to the case numbers, what case number to start at, and how many pages to print. There's also a print button that runs the programming. The second pages is the actual labels that's usually hidden from view.

How we used to format the labels was so they would look like this: DC196221, DC196222, DC196223, etc. To achieve that, on the first sheet you enter DC in the Prefix field and 196221 in the Start # field, and then using references, a formula automatically fills in the second sheet with the correct case numbers.

We want to change our system a bit so that the case numbers look like this: 2008DC000001, 2008DC000002, 2008DC000003, etc. We'd enter 2008DC as the Prefix and then 000001 as the Start #. Or at least, that's what I thought.

When the formula calculates the values for the label sheet, it converts 000001 simply to 1, so the case numbers end up ad 2008DC1, 2008DC2, etc. Is there anyway to prevent that?

Also, if there's a smarter way to format the case numbers, I'm open to suggestions. I don't think we can just use 2008DC1, 2008DC2, etc. because they won't sort properly (all the 1's will sort, including 11, 12, and so on, before all the 2's will sort, when I need them to sort numerically, if that makes sense).

Thanks in advance for your help and suggestions.
 

·
Registered
Joined
·
2 Posts
Discussion Starter #2
So, I found the help I was looking for elsewhere. I didn't know how to format text within a formula but now I do. Thanks for your time.
 

·
Registered
Joined
·
172 Posts
So, I found the help I was looking for elsewhere. I didn't know how to format text within a formula but now I do. Thanks for your time.
How is it done then? It would be handy to be able to have formulas return answers in a specific format...colour,bold etc
 
1 - 3 of 3 Posts
Status
Not open for further replies.
Top