Excel - alphanumeric sequencing

Discussion in 'Business Applications' started by freekrob, Jan 22, 2009.

Not open for further replies.

Joined:
Jan 22, 2009
Messages:
11
I am working on a project to make small labels in a sequential order which allows use to print an excess at a time (50-100+).

Basically, the numbering system is alphanumeric and the label it self has certain characteristics.

the numbers may be listed as XXX01, XXX02... etc.

How I originally did it was to add a custom cell format such as
"[Label]: XXX"general
which would allow me to fill in one number and sequence it down through the follow cells using the simple formula =A2+1 and placing whatever number I wanted it A2. That allowed me to have what I wanted.

The problem with this is that it's too complicated for others in the office to figure out and all custome cell formats would have to be changed individually whenever XXX changed to YYY or whatever.

Additionally, I added another cell into the label for more information which has worked out fine, but I figured everyone likes to know as much as possible. So basically:

A1: Header (unchanged and used on every page, I need no help here)A2: [Label]: XXX100 (A2 and A3 are one label; I want to make XXX100 the first alphanumeric number)A3: [Label 2]: AAA (A3 uses custom cell formating as "[Label 2]"@ so we can change AAA at any time)A4: [Label: XXX101 (A4 and A5 are a second label; A4 would read from A2 as A2+1 (XXX101))A5: [Label 2]: AAA (A5 reads from A3 as =A3 and also uses the same custom cell formating)

So anyway, I didn't know if someone knew a formulla or module I could us for this stuff. Please let me know and I really appreciate your help.

Robert

Joined:
Jan 22, 2009
Messages:
11

A2: [Label]: XXX100
A3: [Label 2]: AAA
A4: [Label]: XXX101
A5: [Label 2]: AAA

Sorry for the messiness.

4. turbodante

Joined:
Dec 19, 2008
Messages:
744
Can you post up a sample file to illustarte what you are trying to achieve?

Joined:
Jan 22, 2009
Messages:
11

Hopefully we can see the above image of what I'm trying to do. Like I said, A1 is set. A2 works with a custom cell format right now as seen in the right side of the screenshot. The numbers I am using are alphanumeric, that being XXX100. Right now all of the similaur cells (A2, A4, A6 etc.) are using the same custom cell formatting ("[Label]: XXX"General), and cells A4, A6, etc are set with =A2+1, =A4+1 etc, respectively. This will get difficult because if I need to change the aplha part of it (XXX) I will have to go to every cell and change the custom cell formatting (to something like "[Label]: YYY"General).

So what I'm looking for, in a nut shell, is a way to simply set my custom cell formatting to "[Label]: "General, and then input a singel aplhanumeric number into cell A2, then to have a formula in the following like cells (A4, A6, etc.) that is =A2+1. This makes it so once i put my number in A2, all of the other like cells change automatically.

So, since I"m not sure how to make aplhanumeric numbers count, I'm stuck.

In the end, the label would looke something like:

[Label]: XXX100
[Label 2]: AAA

[Label]: XXX101
[Label 2]: AAA

[Label]: XXX102
[Label 2]: AAA

The only properties that will change would be XXX100, set to count from cell to cell, and AAA, which is already easily done through a formula of cell2=cell1.

Does that make anymore sense?

6. turbodante

Joined:
Dec 19, 2008
Messages:
744
I see. But I can;t see a way of doing this without the use of a helper column; to contain first of all the text '[Label ]:xxx' and then the numbers 100 to n.

Else, the otherway I can think to do this would be with VBA. Let me know if you need further elaboration with either suggestion.

Joined:
Jan 22, 2009
Messages:
11
I had thought about a help column actually but have not tried it yet. I'm unsure how it will all work out on the label we're using (apx 1.5" by 2"). I actually assumed VBA would have to be used. That's an aspect of excel I never really use so I wouldn't know where to begin. I have looked up some info but getting it under way could prove difficult for me.

8. turbodante

Joined:
Dec 19, 2008
Messages:
744
Have uploaded a sample on how this could work. You could always colour the helper column, in white - or hide the column so it does not print.

If you'd still like to go this route, let me know and I'll see what I can do. Welcome to the forum btw.

Attached Files:

• AlphaNumericSeq.xls
File size:
19 KB
Views:
208

Joined:
Jan 22, 2009
Messages:
11
Well, I worked with going with the helper column a bit but I haven't gotten the chance to test print yet. On the old sheet that I had, it seemed to have some sort of permanent print area where only those cells were shown and every other cell in the worksheet was greyed out. You could put data in them but it would never print. I haven't been able to find that setting anywhere.

Anywho, I would like to try the VBA route as well to get the best looking label I can for professionalism. If you could give me some pointers are codes or what have you, I would appreciate it. Like I said, I really know nothing about creating and using modules and I sure don't know Visual Basic programming code well enough.

10. turbodante

Joined:
Dec 19, 2008
Messages:
744

You can set up what you called a permanent print area using one of Excel's viewing modes - Page Break preview. You can set the print areas accordingly. (See attached file.)

VBA is really just a step up from playing around with Excel's formulas. There's no need tp be worried about modules and wotnots, they're just a place that hold your code, just like sheets are a place to hold your numbers/data.

The best way to learn, is to first have a project in mind - as you have here. The way that I learned was to record a few macros to achive your objects and then examin the code in the VBA editor.

For instance, you could start by recoding a macro of entering '[Label]XXX100' into a cell and see how the code works. Next, I'd have a look at example code prodived in Excel's own help for looping to provide you some sort of automation to loop from 100 to n, to bolt onto the [Label]xxx part of the string.

Attached Files:

• AlphaNumericSeq02.xls
File size:
19 KB
Views:
160

Joined:
Jan 22, 2009
Messages:
11
Ok, I've got the page break preview thing. Thanks for that.

I've been working with VBA as you suggested, recording sessions and checking out the code. I'm pretty much still running into the same issue, that being that I can't seem to get it to actually count the alphanumeric number. I'd like to, ideally, have myself or any one in the office be able to simply type AAA100 into the first sell and have the other cells count from that (AAA101, AAA102... etc.), just as if we were only entering 100 to cell A2 and setting cell A3 as =A2+1. It needs to be that way because AAA will need to change to any of a number of other sequences (BBB, CCC, THW, whatever). Everything else seems like it's working out. I'm continuing my work to figure this out. I haven't found any where else in my search thus far that explains how I can do this. Thanks for your help though!

12. turbodante

Joined:
Dec 19, 2008
Messages:
744
The problem with using the formula =A1+1 to make your increment is that Excel is attempting to add 1 to the contents of the cell, which is a string. If the way you want to work is to enter the string label once, in cell A1 - then one suggest is to split the contents out into a two part formula. A fixed prefix (AAA) as part one; for the next part increment the three figure suffix (100).

[in Cell A1] AAA100

[Cell A2 and dragdown ] =LEFT(A1,3)&RIGHT(A1,3)+1

Joined:
Jan 22, 2009
Messages:
11

That formula pretty much works perfectly for what I need. There are two hitches I've come across however, both of which I don't think I've mentioned.

A) the problem of leading zeros
When I'm using the formula AAA001, it comes up as AAA2, AAA3 etc in the follow cells. I've tried a could of things to remedy this but haven't found a fixed solution yet.

B) the change in the amount of numbers in the alphanumeric label
That is, what happens after AAA999? I've been trying to look into a way to have excel sense the change automatically but it seems everything I come up with has a problem or an error.

Thanks for the help. Things are starting to look a little more correct now.

14. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Excel assumes that leading zeros are nonsense, unless the data is marked as text. So if you format your column as text and then type in numbers starting with 0 it will retain them.
Personally, I would use two columns - in A I would enter XXX and fill as needed. In B I would format the column as text, then enter 001 in B1, 002 in B2 and fill down as needed. When you get to 999, the next number will be 1000. If you want all the items to have the same number of characters, start with 0001, 0002 etc. (or add more zeros if you think that is possibly needed)
Finally, in C1 I would enter
=(A1&B1)
This will give you the results in C of
XXX001, XXX002....

15. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
After having done the above, copy column C, then over the same data do a Paste Special, Values. This will convert the formulas to the combined data (e.g. XXX001.) Then delete the data in columns A and B. Save your file.
I would then suggest you get ASAP Utilities from http://www.asap-utilities.com/ (it is a free and very useful add-in for Excel.)
After you have installed it, you will find that there is a handy tool under #6 Columns and Rows, option #6 called Insert in-between empty rows or columns.
Highlight the data you have in column A now (XXX001 through XXX1500 or whatever it is.)
Click on ASAP Utilities in Excel, then go to the aforementioned insertion option. Select Rows.
ASAP will put a blank row between every row in your selection. Delete the one between your header and XXX001.
Now, I am not sure if you can do a Replace, but, if you can, replace all the blanks in your list with [Label 2]: AAA

16. turbodante

Joined:
Dec 19, 2008
Messages:
744
You can solve the problem of leading zeros with this alteration to the formula

Code:
```[SIZE=2]=LEFT(A1,3)&IF(LEN(RIGHT(A1,3)+1)=1,"00"&RIGHT(A1,3)+1,(IF(LEN(RIGHT(A1,3)+1)=2,"0"&RIGHT(A1,3)+1,(IF(LEN(RIGHT(A1,3)+1)=3,RIGHT(A1,3)+1,">EE")))))[/SIZE]
```
What would you like to happen after AAA999, in the formula above I have left it to return the error ">EE". But I'm sure we can cobble up something to look more professional.

As Seen On