Formula to calculate the next invoice number

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,676
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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"))))
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
Tony, Rick,

Here is another possibility...
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">abscdftererePRB1009</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">abscdftererePRB1010</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">A1,-LOOKUP(<font color="Red">1,-RIGHT(<font color="Green">A1,ROW(<font color="Purple">INDEX(<font color="Teal">A:A,1</font>):INDEX(<font color="Teal">A:A,LEN(<font color="#FF00FF">A1</font>)</font>)</font>)</font>)</font>),-LOOKUP(<font color="Red">1,-RIGHT(<font color="Green">A1,ROW(<font color="Purple">INDEX(<font color="Teal">A:A,1</font>):INDEX(<font color="Teal">A:A,LEN(<font color="#FF00FF">A1</font>)</font>)</font>)</font>)</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I'll show you mine if you show me yours! ;)
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,676
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Brilliant, thanks guys, both formulas work perfectly
:)
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634

ADVERTISEMENT

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?
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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,"")
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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.
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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
Top