# Incrementing value by 1 from text in cells on
Hi Guys,

I need your assistance. I have the following four rows of text in Exel from A1 to A4

WHEN LENGTH(TRIM(TRANSLATE(substr(re.FIELD1,2,6), ' +-.0123456789',' '))) is NULL AND u.sales_b = substr(re.FIELD1,8,2)
THEN substr(re.FIELD1,2,6)
WHEN LENGTH(TRIM(TRANSLATE(substr(re.FIELD1,2,6), ' +-.0123456789',' '))) is NOT NULL AND u.sales_b = substr(rtrim(re.FIELD1),-2)
THEN SUBSTR(re.FIELD1, 2, LENGTH(re.FIELD1) - 3)

I would like to increment the value of re.Field1 by 1 every time I drag the cells so it displays re.Field2, re.Field3, re.Field4 and so on but the formula below works only when displaying re.Field2 but after this I get disperse values rather than incrementing by 1. I have the next four rows of text from row A5 to A8.

="WHEN LENGTH(TRIM(TRANSLATE(substr(re.FIELD"& ROW()-ROW(\$A\$4)+1 & ",2,6), ' +-.0123456789',' '))) is NULL AND u.sales_b = substr(re.FIELD"& ROW()-ROW(\$A\$4)+1 & ",8,2) "
="THEN substr(re.FIELD"& ROW()-ROW(\$A\$5)+1 &",2,6)"
="WHEN LENGTH(TRIM(TRANSLATE(substr(re.FIELD"&ROW()-ROW(\$A\$6)+1 &",2,6), ' +-.0123456789',' '))) is NOT NULL AND u.sales_b = substr(rtrim(re.FIELD"& ROW()-ROW(\$A\$6)+1 & "),-2)"
="THEN SUBSTR(re.FIELD"&ROW()-ROW(\$A\$7)+1 &",2, LENGTH(re.FIELD"&ROW()-ROW(\$A\$7)+1 &") - 3)"

Comment
Watch Question

Do more with EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi

You could replace

=ROW()-ROW(\$A\$4) with

=ROUNDUP((ROW()-ROW(\$A\$4))/4,0)

or maybe better
=ROUNDUP(ROW()/4,0)

Regards

Commented:
Thank you. This one work for me.

=ROUNDUP(ROW()/4,0)

Do more with 