• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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