Find next number in "sequence"

Hi all,

I use a list of "part numbers", (NB Stored as text), in a spreadsheet, and I need to find the next avaiable part nunber. This would normally be fairly straightforward, but I need to know the next available part number at tjhe end off a specific "range",

ie the main list would be:-

1001aa
1002aa
5001aa
5002ba
9001aa
9002aa,

and I need to find the next available number in the "5000" range, (ie in the above case this would be "5003"?

Any help appreciaed,

Many thanks,

Stu

Hi Stu

Given your sample data (assume in col A) then maybe:

=5000+COUNTIF(A:A,"5*")+1

this assumes that you have no part numbers 501ab for example though.

Richard

Here is one option.

I wasn't sure if all part numbers all had that format or if it was just an example.
Meaning the layout can be improved.

Hope it helped
Ola

Hi Richard...thanks for the reply, but unfortunately, there could be a 5010 in the series, although I will work on your suggestions and see if I can get sometheing similar to work..

ta,

Stu

Hi Olasa..what a mind blowing sollution!!!!

I could not have thought of that in a million years ...you are obviously well versed in excel....brilliant.

I will go with your that

..many thanks,
Stu

Happy to have saved you a million years
Thanks for the feedback
Ola