Formula to calculate the next invoice number

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi everyone,
I have a formula in cell A1 that looks at my data sheet and finds the last invoice number
What I need is in cell A2 a formula that will calculate the next invoice number.
So if the result in A1 was PRB100 A2 Would show PRB101
now the problem I have is the text part can change in size it can be PRB100, but it could be KLMUA10 OR AD10002 basically I have no way to tell how many letters there will be before the numbers but I just want it to add 1 to the number part.

Any ideas how i could do this? *
Thankyou
Tony
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Seems like there should be a simpler formula than this, but this is what I came up with...

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)&TEXT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))+1,REPT("0",1+LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))))
 
Upvote 0
Tony, Rick,

Here is another possibility...

Excel 2007
A
1abscdftererePRB1009
2abscdftererePRB1010
Sheet5
Cell Formulas
RangeFormula
A2=SUBSTITUTE(A1,-LOOKUP(1,-RIGHT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))),-LOOKUP(1,-RIGHT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))+1)
 
Upvote 0
Tony, Rick,

Here is another possibility...
Excel 2007
A
1abscdftererePRB1009
2abscdftererePRB1010

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
A2=SUBSTITUTE(A1,-LOOKUP(1,-RIGHT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))),-LOOKUP(1,-RIGHT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))+1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

For "JAN01" I get "JAN01". Is it my date settings?
 
Upvote 0
Give this a try:

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)&REPLACE(-LOOKUP(1,-("111"&RIGHT(A1,{1,2,3,4,5,6,7,8,9,10,11,12})))+1,1,3,"")
 
Upvote 0
Istvan,

Good spot and nothing to do with your date settings.

My formula is fine unless it encounters an invoice number that ends with e.g......

JAN01, XXXOctob1, YYYYDec03 i.e. the ending equates to text for full or abbreviated month followed by one or two digits, such that it could represent a date.

In those instances the lookup returns the equivalent serial date e.g. 38626 for October 1st ! Thats value does not exist in the original string and so the substitution can not take place. Thus the formula returns the original string.
 
Upvote 0
Istvan,


JAN01, XXXOctob1, YYYYDec03 i.e. the ending equates to text for full or abbreviated month followed by one or two digits, such that it could represent a date.

In those instances the lookup returns the equivalent serial date e.g. 38626 for October 1st !

Not if the actual value to LOOKUP is preceded by, for example, „111”, which is later deleted by a Replace function.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top