How to increment value in the middle

[Bejoy1983]

Hey all,

So I need help in finding out a method to automatically increment a centre value. So for example my value is XXX/0001/2022-23 and the value 001 should increase incrementally.

[#2 · Accepted solution]

@Bejoy1983 

="XXX/"&TEXT(ROW(1:1),"0000")&"/2022-23"

Maybe with this formula.

[#3]

Thank you so much

[#4]

Ok so now faced another issue.
HC/0243/2022-23
HC/0244/2022-23
HC/0245/2022-23
HC/0246/2022-23
HC/0247/2022-23
HC/0248/2022-23
HC/0249/2022-23
HC/1240/2022-23
HC/1241/2022-23
HC/1242/2022-23

After 0249 instead of 0250 it goes to 1240. So basically i need to start from the number 0240 and not 0001.

[#5]

@Bejoy1983 

="HC/"&QUOTIENT(ROW(1:1)-1,10)&"24"&IF(MOD(ROW(1:1),10)=0,9,MOD(ROW(1:1),10)-1)&"/2022-23"

Is this what you are looking for?

 

[#6]

No. What i need is for the numbers to increment normally.
I am starting from 0240 and it should continue normally as
0240
0241
0242
0243
0244
0245
0246
0247
0248
0249
0250
0251 etc.

But what you shown above is what I am getting. Instead of 0250 it goes to 1240 after 0249.

[#7]

@Bejoy1983 

="XXX/"&TEXT(ROW(1:1),"0000")&"/2022-23"

Does this mean you had an issue with the first suggested formula? The formula returns the expected result in my sheet.

If you want to start with 0240 you can apply this formula:

="XXX/"&TEXT(ROW(240:240),"0000")&"/2022-23"

[#8]

So basically that formula works but after 0249 it goes to 1240 instead of 0250.

[#9]

@Bejoy1983 

Can you attach a screenshot which shows the results in your sheet and the formula?

[#10]

My bad. Changing it to (241:241) solved the issue.