Solved increase cell value by one from previous cell

Microsoft Office 2007 home and student
October 6, 2011 at 09:29:26
Specs: Windows Vista
I would like formulas in cells A5 to A37 that increase by one number.

For example:
In cell A4 I have an item number of A2196.
I want a formula in cell A5 that will show a result of A2197 in that cell.
Then a formula in cell A6 that will show A2198...etc.

I do not want to use the fill handle as this sheet will be changed and printed with different numbers constantly, so I just want to have to enter one starting number in cell A4 and have the rest automatically updated.

Is this possible?


See More: increase cell value by one from previous cell

Report •

✔ Best Answer
October 6, 2011 at 12:08:54
The reason it's "difficult" is because you are asking Excel to manipulate Text Strings as if they were numbers.

Excel was not written to be text friendly since it's a numbers oriented application.

The formula below will work as long as there is only 1 leading zero in your 5 character string and no leading zeros in you6 6 character string.

e.g.

A0123 and AZ1234 are OK but A0012 and AZ0123 are not.

In addition, it will fail soon after A9999 and AZ9999. It will return A10000 and AZ10000 respectively, but that's as far as it will work correctly.

The failing conditions could be dealt with, but the formula will just keep getting longer and longer as we include clauses to check for all the possible places that a zero could occur.

=IF(LEN(B4)=5,IF(MID(B4,2,1)="0",IF(RIGHT(B4,3)<>"999",LEFT(B4,2)&RIGHT(B4,3)+1,LEFT(B4,1)&RIGHT(B4,4)+1),LEFT(B4,1)&RIGHT(B4,4)+1),LEFT(B4,2)&RIGHT(B4,4)+1)

I may play around a bit to see if there is a better way, but I'm not sure that there is.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 6, 2011 at 10:02:50
It's possible but it will take some finessing of the original value.

re: "I...want to...enter one starting number "

In the eyes of Excel, A2196 is not a "number", it's a text string (because of the "A") therefore, as I'm sure you are aware, =A4 + 1 won't work.

The fill handle works because Excel is "smart enough" to recognize that you want a Series and increments the numeric portion of the string for each cell.

However, if your string is always going to be a single letter followed by 4 numbers, then dragging this down should work:

=LEFT(A4,1) & RIGHT(A4,4)+1

If your part number will not always be of that format, it might be hard to come up with a single formula that will work for all alpha-numeric combinations.

If you'll give us a few more examples, we'll see what we can do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 6, 2011 at 10:51:38
Close. That formula works except for one problem. If the number inserted in A4 has a zero as the first digit, it does not show.

Example: If I type B0325 in cell A4 and then with the formula, cell A5 shows B326. It should read as B0326.

As for the string type. It will be different on each sheet I create. There will also be some that have two letters followed by four numbers. (ie. AZ1280).

If the value in cell A4 is AZ1280, would I use the formula:
=LEFT(A4,2) & RIGHT(A4,4)+1
?


Report •

#3
October 6, 2011 at 11:05:14
Will any values of the type AZ1280 have that pesky leading 0?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 6, 2011 at 11:10:06
Not so far. There may be at some point, but not yet... so I think it's safe to say no...

Report •

#5
October 6, 2011 at 11:49:38
As I was working on this I realized other possible problem:

If the value is A0999 are you going to want A1000? That's going to get messy.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 6, 2011 at 11:51:52
Yes I would... I didn't realize it was going to be this difficult... :-(

Report •

#7
October 6, 2011 at 12:08:54
✔ Best Answer
The reason it's "difficult" is because you are asking Excel to manipulate Text Strings as if they were numbers.

Excel was not written to be text friendly since it's a numbers oriented application.

The formula below will work as long as there is only 1 leading zero in your 5 character string and no leading zeros in you6 6 character string.

e.g.

A0123 and AZ1234 are OK but A0012 and AZ0123 are not.

In addition, it will fail soon after A9999 and AZ9999. It will return A10000 and AZ10000 respectively, but that's as far as it will work correctly.

The failing conditions could be dealt with, but the formula will just keep getting longer and longer as we include clauses to check for all the possible places that a zero could occur.

=IF(LEN(B4)=5,IF(MID(B4,2,1)="0",IF(RIGHT(B4,3)<>"999",LEFT(B4,2)&RIGHT(B4,3)+1,LEFT(B4,1)&RIGHT(B4,4)+1),LEFT(B4,1)&RIGHT(B4,4)+1),LEFT(B4,2)&RIGHT(B4,4)+1)

I may play around a bit to see if there is a better way, but I'm not sure that there is.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
October 6, 2011 at 12:24:23
I changed the B4 to read A4 and the formula works great. I have tried the different starting numbers I currently have and the formula works for each.

Usually I can better understand and follow formulas once I see them, but I must admit that most of this one is beyond anything I've ever used before.

Thank you very much for your help with this. It is greatly appreciated. :-)



Report •

#9
October 6, 2011 at 13:00:44
It's actually quite simple. It's mainly of matter of keeping your value_if_true and value_if_false arguments for all of the Nested IF's straight.

First, it checks to see if the Length of the string is 5 characters.

If it is, it checks to see if the second character is a zero. I had to use quotes ("0") since the MID function returns a Text 0 not a numeric 0.

If the second character is a 0, it checks to see if the last 3 characters are NOT "999"

If all of those conditions are TRUE, then it evaluates LEFT(B4,2)&RIGHT(B4,3)+1 returning the A0 and then adding 1 to last 3 characters.

If the <> "999" is FALSE, then it evaluates LEFT(B4,1)&RIGHT(B4,4)+1 returning the A and 1000. As it turns out, LEFT(B4,1)&RIGHT(B4,3)+1 will work also since the leading 0 is ignored anyway.

If the "0" condition wasn't TRUE, then it skips over to the next LEFT(B4,1)&RIGHT(B4,4)+1 and returns A and the four digits plus 1.

Finally, if the Length wasn't 5 (e.g. AZ1234) then it skips all the way to the end (the value_if_false for the IF(LEN...) function) and evaluates LEFT(B4,2)&RIGHT(B4,4)+1 returning AZ and 1234 plus 1.

If you want to follow the formula as it performs its evaluations step by step, click on the cell with the formula and use the Formula Auditing...Evaluate Formula tool.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question