• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

increment value by 1 for each row

How can I increment a cell value by 1 in each new row?

for example, let's say I have the value DE01 in cell A1.  I want to copy that down 500,000 rows and have it increment by 1 on each new row, so cell A2 would be DE02, A3 would be DE03, etc...

How can I do this?

Thanks.
0
-Dman100-
Asked:
-Dman100-
1 Solution
 
Tony303Commented:
forget my last post, letters aren't playing ball, like numbers do.
0
 
Saqib Husain, SyedEngineerCommented:
=LEFT(A1,3)&SUBSTITUTE(A1,LEFT(A1,3),"")+1
0
 
Saqib Husain, SyedEngineerCommented:
Another method is to enter this in A1 and copy down

="DE0"&ROW()

You can also fill the numbers 1, 2, 3, ....
and then custom format them with the string

"DE0"general
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Patrick MatthewsCommented:
To just display the "DE", then enter 1 in A1, =A1+1 in A2, and copy that formula down to A500000.  Then apply a custom number format of"

"DE"00

To actually make the DE part of the value, enter this in A1, and copy down to A500000...

=TEXT(ROW(),"""DE""00")
0
 
Tony303Commented:
I tried this in Cell A2.

=CONCATENATE("DE",RIGHT(A1,LEN(A1)-2)+1)

Then filled down.
If you don't care about the "leading zero" then this is good enough.
0
 
jwlemmonCommented:
All the above will work and you just need to adjust if you need it to be "DE0"number or "DE"number
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now