Apply formula to an entire column

aerybritney

New Member
Joined
Oct 24, 2014
Messages
2
This is the formula list :

PV001/10/14
PV002/10/14
PV003/10/14

<tbody>
</tbody>

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,568
Office Version
2010
Platform
Windows
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)
 

Forum statistics

Threads
1,085,359
Messages
5,383,171
Members
401,816
Latest member
Balearic

Some videos you may like

This Week's Hot Topics

Top