Rao Ratan Singh wrote:
But my data is more than 1005, then what will I do.

Hi RRS,

If your data count exceeds 1005 then you should have started with an ID
number pattern that can handle more than 999 data items.

Say your data count is not going to exceed 9999...

then the first ID number you should enter into A6 is 0001-2007, and the
formula starting in A7 is...

=REPT("0",3-INT(LOG(LEFT(A6,4)+1))) &(LEFT(A6,4)+1) & "-2007"

which differs from the previous formula only by a 2 changed to a 3 and
two 3s changed to 4s.

The above does not produce the #VALUE error until row 10005.

Similarly...

=REPT("0",4-INT(LOG(LEFT(A6,5)+1))) &(LEFT(A6,5)+1) & "-2007"

will cater for 99999 ID numbers, and...

=REPT("0",FIND("-",A6)-2-INT(LOG(LEFT(A6,FIND("-",A6)-1)+1))) &
LEFT(A6,FIND("-",A6)-1) +1 &"-2007"

will cater for however many leading digits you use in the A6 entry,
since it uses the find function to determine the correct values to use
in the incrementing formula. Note, however, that the assumption is made
that the only character between the counting digits and the trailling
2007 is the "-" character.


Ken Johnson