Retaining leading zeros in text cell problem

[PaulC]

I am trying to put the next consecutive number into the cell below. The column is formatted as text and I need to retain the leading zeros e.g.

I have simply used

Please Login or Register  to view this content.

. This works fine for, say, adding 1 to 3939 to give 3940, but if I try with 0001, I always get just 2 without the leading zeros. I have tried using the Format and Str functions with no success.

I am sure there must be a simple solution to this.....

Paul

[royUK]

Use a Custom Format - 0000

[mikerickson]

Try

Please Login or Register  to view this content.

Automatic type conversion, as in your

Please Login or Register  to view this content.

is nice, but when things don't work right, specifing the heck out of everything is a good first step.


P.S. "Please wrap code" includes even snippets like the one in your original post.

[PaulC]

Many thanks for your help. Sorry not to reply early but Christmas intervened...

I hadn't mentioned but I also needed to select just the first 4 characters from the row above as they occasionally have a letter at the end. After adding the Left funtion, I couldn't get the code you gave to work without removing the Val function, but this does now work:

Please Login or Register  to view this content.

Thanks for your tip about specifing everything and I will wrap all code in future,

Paul