Apply formula to an entire column

[aerybritney]

This is the formula list :

[TABLE="width: 122"]
<tbody>[TR]
[TD]PV001/10/14[/TD]
[/TR]
[TR]
[TD]PV002/10/14[/TD]
[/TR]
[TR]
[TD]PV003/10/14
[/TD]
[/TR]
</tbody>[/TABLE]

The next sequence should be PV004/10/14. Currently, when i drag, the number will turn up PV003/10/15 instead of PV004/10/14.

Is there any specific formula for the above - for me just to drag down the box and fill up the column?

 

[Rick Rothstein]

Put your starting value (PV001/10/14) in the first cell (assumed to be A1), then...

If your first two letters will always be "PV" and the last 6 characters will always be "/10/14" whenever and wherever you have need to increment it, then put this formula in A2 and copy down...

=TEXT(MID(A1,3,3)+1,"PV000""/10/14""")

If, on the other hand, in different uses, the first two, and/or the last six, characters might be different in some other location where you need to increment that "shaped" value (for example, maybe the next time you have to increment something like this, starting value is different, like XZ001/99/88 for example, then use this formula instea...

=LEFT(A1,2)&TEXT(MID(A1,3,3)+1,"000")&MID(A1,6,6)

 

[aerybritney]

Thank you very much!!!!