Help creating a formula that repeats text & increments an integer

  • Thread starter Thread starter djow
  • Start date Start date
D

djow

I been trying to create a formula that repeats the same text characters in
each consecutive cell in a column but increments the integer by one (see
example below):
A1 = AB-0
A2 = AB-1
A3 = AB-2...

I believe I can take care of the text by using the function =LEFT(A1,3) but
I haven't been able to figure out how to add the formula to increment the
integer. I tried to just add "+1" at the end of the formula, but got the
#VALUE! error. I tried searching past posts and the Excel help menu, but
didn't see any entries that addressed this question.

Thx in advance for any help
 
Here's one way (placed in A2, with A1 containing AB-0 as shown):

=LEFT(A1,3)&ROW(A1)

Copy down as required. If you want leading zeros for the number, so
the entries are all the same length, then you could use:

=LEFT(A1,3)&TEXT(ROW(A1),"000")

which will give you AB-001, AB-002, AB-003 etc.

Hope this helps.

Pete
 
Of course you can increment this by just dragging the fill handle down, no
formula necessary.

If you want a formula enter

="AB-"&ROW()

in A1 and copy it down.

The formula =LEFT(A1,3)&TEXT(RIGHT(A1,1)+1,"0")
will not work after 9.
 
Back
Top