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

# 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-
1 Solution

Commented:
forget my last post, letters aren't playing ball, like numbers do.
0

EngineerCommented:
=LEFT(A1,3)&SUBSTITUTE(A1,LEFT(A1,3),"")+1
0

EngineerCommented:
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

Commented:
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

Commented:
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

Commented:
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.

## Featured Post

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