Increment values in a special manner

[shaju]

Hi,
I have a number which goes like XXXXX/dd-mm-yy. What I want is that when this number is entered into a cell and then dragged down, the XXXXX part should get incremented by one number each but the /dd-mm-yy should remain the same. Is it possible...???
TIA

 

[MrKowz]

Try:

Excel 2013/2016
	A
1	12345/01-02-17
2	12346/01-02-17
3	12347/01-02-17
4	12348/01-02-17
5	12349/01-02-17
6	12350/01-02-17
7	12351/01-02-17
8	12352/01-02-17
9	12353/01-02-17
10	12354/01-02-17
11	12355/01-02-17
Sheet9

Cell Formulas
Range	Formula
A2	=VALUE(LEFT(A1,SEARCH("/",A1)-1))+1&"/"&RIGHT(A1,LEN(A1)-SEARCH("/",A1))

 

[Rick Rothstein]

Assuming your number part could start with one or more leading zeroes, place your starting value in cell A1 (formatted as Text), then put this formula in cell A2 and copy it down...

=TEXT(LEFT(A1,5)+1,"00000")&MID(A1,6,16)

 

[Jonmo1]

If the date is ALWAYS in the dd-mm-yy format

Try

=LEFT(A1,LEN(A1)-9)+1&"/"&RIGHT(A1,8)

 

[shaju]

Thank You All...............Now the real problem is which method to select....Thank You all once again.

 

[Rick Rothstein]

Thank You All...............Now the real problem is which method to select....Thank You all once again.

If your leading number could ever start with one or more zeroes, then the formula I posted in Message #3 is the only formula that will retain the leading zeroes. If the number can never start with a zero, then any of the formulas will work.

 

[MrKowz]

Thank You All...............Now the real problem is which method to select....Thank You all once again.

Cheers - happy we were able to find a working solution for you. If it helps to figure out which method to select, the one I posted allows you to be a bit more dynamic with your entry. Jonmo and Rick's solutions are great if you meet the specified criteria they ouline. For Rick's if there are always 5 numbers leading, his will calculate faster and is less complex (edit: and will maintain leading 0's). For Jonmo's, if your date is always dd-mm-yy format, his will be able to iterate a differing length of numbers at the beginning, and again is faster and less complex than mine.

See example below for different example inputs and the outputs of each formula method:

Excel 2013/2016
	A	B	C	D	E
1		Inputs
2		123/01-02-17	12345/1-2-17	12345/01-02-17	00123/01-02-17
3	MrKowz	124/01-02-17	12346/1-2-17	12346/01-02-17	124/01-02-17
4	Rick	#VALUE!	12346/1-2-17	12346/01-02-17	00124/01-02-17
5	Jonmo	124/01-02-17	124/5/1-2-17	12346/01-02-17	124/01-02-17
Sheet11

 

[Rick Rothstein]

For Rick's if there are always 5 numbers leading

Good point mentioning that because I forgot to.

The reason I assumed there had to be 5 leading digits is because the OP showed 5 X's (twice) at the beginning of his representation of what should be in the cell.

 

[Jonmo1]

Channeling my best Horshack impression..

Ooh! Ooh! Ooh! Ooh!!!! Pick Me..

Lol, seriously though.
If it's safe to assume there are no leading/trailing spaces, and the date will ALWAYS be dd/mm/yy, then I think mine will be the simplest and most efficient.

 

[MrKowz]

Good point mentioning that because I forgot to.

The reason I assumed there had to be 5 leading digits is because the OP showed 5 X's (twice) at the beginning of his representation of what should be in the cell.

I got your back, Jack.