Add text to formula result

[Mr_Adams]

I use this formula to give me new invoices numbers each time the worksheet is opened.

=MAX('\\Ice\Fin_Data\Invoices\2003\[Finance Invoice Ledger 2003.xls]Finance Invoice Ledger 2003'!$A:$A)+1

How can I put text in front of the result and insure the numeric value is 5 didgits.

Example:
The formula result is 560.
But I want the result to be FIN-00560.

Thank you for any help.

 

[Juan Pablo González]

Try with

="FIN-" & TEXT(MAX('\\Ice\Fin_Data\Invoices\2003\[Finance Invoice Ledger 2003.xls]Finance Invoice Ledger 2003'!$A:$A)+1,"00000")

 

[TommyGun]

If it's only for display purposes, then you could use this custom number format with your formula..."FIN-"00000

 

[Mr_Adams]

That worked perfect, Juan. However there is another problem that I didn't anticipate. I use the template wizard to save records. The invoice number that it stores for the above example is FIN-00560. But when the worksheet is opened next time the result is FIN-00001 becuase FIN-00560 is entry the formula is trying to add 1 to. any ideas on how to correct this.

 

[Juan Pablo González]

Try using Tommy's suggestion, I like it better too

 

[Mr_Adams]

Thanks tommy that was a good solution.
But when the worksheet is saved it is saved as 560 instead of FIN-00560.
Any Ideas on how to change the following code to save as the complete invoice number

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' *******************************************************************
' * Forces Database update and saves new file as the Invoice Number *
' *******************************************************************
Application.Run "commit"
ActiveWorkbook.SaveAs FileName:="\\Ice\Fin_Data\Invoices\2004\Invoices\" & Range("L13")
End Sub

 

[Juan Pablo González]

Put a

.Text

at the end of that line of text.

 

[Mr_Adams]

Sorry i'm still learning. I don't know what that means.

 

[Juan Pablo González]

ActiveWorkbook.SaveAs FileName:="\\Ice\Fin_Data\Invoices\2004\Invoices\" & Range("L13").Text

 

[Mr_Adams]

Thank You

I see that it works, but I'm not sure I inderstand. does the .Text mean what is displayed in the cell instead of the the formula result is, since what is displayed is different?