Custom Number Formatting

Hi There,
(PBDU2338842001) – this is a consignment number and the prefix before the number is a standard lettering but does not vary, however the number after the prefix are randomly generated, what i am trying to do is to generate 10 different consignment numbers with having the last number on the right of cell changed.
Consignment Number Number of Characters in a cell Criteria
PBDU2338842001 =LEN (PBDU2338842001) = 14 10
Example: PBDU2338842001, 002,003,004,005 and so on,
I know we can use the fill handle to drag it down and it would do this, but in some instance it does work, is they any way of adding the last three numbers with having the criteria of 10 or 5
If a cell has PBDU2338842001, and 10 of these items
PBDU2338842001
PBDU2338842002
PBDU2338842003
PBDU2338842004
PBDU2338842005
PBDU2338842006
PBDU2338842007
PBDU2338842008
PBDU2338842009
PBDU2338842010
PBDU2338842011
For example if PBDU2338842001 has 10 items, then it should calculate the formatting as above, i have been trying to do this with custom formatting, but not very successful with it, please any one can show some guidance as I am very novice to excel formula’s.

I have attached a sheet for guidance

Hi there

which means the consignment number is 1 of 10 items

2 of 10
3 of 10
4 of 10

and so on

avid learner

i have a consignment number as PBDU2338842001 which is 14 characters in length, this is a consignment number of an item which has a critieria or number of items of the same consignement the only part that changes is the last three digit 002, 003 , 004 and so on.

so it would look like this PBDU2338842001 AND PBDU2338842002

So another words, you have an item coming to you with 10 items

I HOPE YOU UNDERSTAND

HUSTON

Dear Friend,

Thanks ever so much for the solution, it works

huston,

Hi there again,

=IF(ROW(A1)>$G$3,"","PBDU2338842"&TEXT(ROW(A1),"000"))

that is great, but if the PBDU2338842 was a different number to this on different row or column, would it still work
huston

Hi There,

once you open the sheet the one you have generated, it works for the first row1, but for the 2nd row the consignment number is different to row1 and the criteria is also differerent, how would you insert an extra rows to accomadate the 2nd row solution and having the different critieria.

huston

Hi Martin,

Would it be possible to have them in a single column, or from the result calculated, could you be able to transpose each row into one single column with each rows of consignment followed by next number:

Example
PBDU2338842001
PBDU2338842002
PBND3566466001
PBND3566466002
PBND3566466003
PBND3566466004
PBND3566466005
PBND3566466006
PBND3566466007
PBND3566466008
PBND3566466009
PBND3566466010
PBND3566466011
huston

Martin

Thank you for your support on resolving the problem,

once again thank you

huston