Forum Discussion

Bejoy1983

Copper Contributor

Sep 03, 2022

Solved

How to increment value in the middle

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.

excel

Formulas and Functions

OliverScheurich
Sep 03, 2022
Bejoy1983
="XXX/"&TEXT(ROW(1:1),"0000")&"/2022-23"
Maybe with this formula.

Bejoy1983

Copper Contributor

Sep 04, 2022

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.

OliverScheurich

Gold Contributor

Sep 04, 2022

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?

Bejoy1983
Copper Contributor
Sep 04, 2022
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.
- OliverScheurich
  Gold Contributor
  Sep 04, 2022
  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"
  - Bejoy1983
    Copper Contributor
    Sep 04, 2022
    So basically that formula works but after 0249 it goes to 1240 instead of 0250.