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.

OliverScheurich

Gold Contributor

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"