Consequential with 2 item numbers

[RJ1969]

Hi

What formula should I used to consequential with a 2 items in a cell?
Let say In column A1.
From AZ129601 to AZ129700 and the next should be AZ129701 to AZ129800, AZ129801 to AZ129900, and so on. There is a 100 item between the item numbers.

I have this code =IF(A1="","",REPLACE(A1,LEN(A1)-1,2,TEXT(RIGHT(A1,2)+1,"00")))
But it is not working the way I wanted with these items.

Thanks.

[Richard Buttrey]

Hi,

This may be a much simplified example and may not represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

That said if the initial alpha characters are always two in number and the number is always 6 digits then

Please Login or Register  to view this content.

[RJ1969]

Hi,
It works but what when I tried it with AZ009601 to AZ0009700.


Thanks.

[Richard Buttrey]

As I said we need to be told all all the permutations of data you may have and what you expect as a result.

What do you want to see?

What about perhaps AZ000091, or AZ0000901, or AAZ129701, are those possible strings?

Are there any other permutations?

[RJ1969]

Hi,
We actually started AZ000001 to AZ000100 and it will go up to probably to AZ159999. There is always a two letter in the front and 6 numbers.

Thanks.

[JeteMc]

This looks similar to your thread "Wrong Sequence number" found at https://www.excelforum.com/excel-pro...ml#post5393896
There is a post there which I feel resolves both issues and to which you have not responded.