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?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,904
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,098,976
Messages
5,465,763
Members
406,449
Latest member
cubeincube

This Week's Hot Topics

Top