Automatic Part Number Generator

[Teddydoggy]

Hello all,

First time posting here. I'm trying to create a part number generator in Excel. The part numbers are in the following format: "XXXXX-XX", where the first five digits are serialized numbers, and the trailing two digits are the revision level.

When a new part number is pulled, the revision level will always start from "01". e.g:"12345-01". I already have pre-existing part numbers in column "A", and my goal is to have Excel look through the entire "A" column, break the part numbers up and only look at the first 5 serialized digits, then identify the highest value, and automatically +1 to that value. And finally, add the trailing revision level "-01" to the newly generated part number. If my most recent part number was "12345-22", then a newly generated part number would be "12346-01".

This is what I have so far:

[/FONT]=MAX(VALUE(LEFT(A1:A11, 5))) +1 & "-01"[FONT=arial]

The code above works as long as I manually update the range (A1:A11). However, the part number list will be growing, so is there a way to make the input range dynamic? I've already tried (A:A), which includes all the empty cells in column "A", and the "Max" function returns "0" if there are any empty cells in your defined range. I also tried embedding a "Counta (A:A)" function in the formula but kept getting syntax errors.

Also, if I wanted to convert this into a VBA, and have the whole process run in the background, would I be able to simply copy the formula above into VBA? My end goal would be to just simply click a button, and the Macro automatically inserts a new row with the new part number.

Any help would be appreciated! Thanks in advance.

 

[Rick Rothstein]

You can use this array-entered** formula although it would be more efficient if you decided on a maximum row number you would never exceed and use that in a fixed array in place of the A:A reference...

=MAX(IFERROR(1+LEFT(A:A,5),0))&"-01"

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Teddydoggy]

Thank you Rick. That worked beautifully!

You can use this array-entered** formula although it would be more efficient if you decided on a maximum row number you would never exceed and use that in a fixed array in place of the A:A reference...

=MAX(IFERROR(1+LEFT(A:A,5),0))&"-01"

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself