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?

✔ 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.

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.

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

?

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.

Not so far. There may be at some point, but not yet... so I think it's safe to say no...

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.

Yes I would... I didn't realize it was going to be this difficult... :-(

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.

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

It's actually quite simple. It's mainly of matter of keeping your value_if_trueandvalue_if_falsearguments 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_falsefor 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.

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History