Get number's alphabetic equivalent?

[Saubs]

My solution has invoice numbers with five digits followed by a letter. I'd like to let my users preview what the subsequent invoice number in a particular series would be, before it's created (why? Long Story).

In other words, if invoices 20000a, 20000b, and 20000c exist, then I want a tooltip to show that the next invoice to be created in the 20000 series would be 20000d. Auto-enter serial numbers are not being used, btw.

I'm working on a formula that's getting me close:

Let ( [

trailingLetter = Right ( invoiceNumber ; 1 ) ;

trailingLetterAsNumber = Position ( "abcdefghijklmnopqrstuvwxyz" ; trailingLetter ; 1 ; 1 ) ;

trailingLetterPlusOne = trailingLetterAsNumber + 1 ;

subsequentLetter = GetAsText ( trailingLetterPlusOne ) ] ;

Left ( invoiceNumber ; 5 ) & subsequentLetter & " is the next invoice in this series." )

I need to get the next-to-last line of this formula to convert the number to a letter. Obviously, GetAsText isn't going to work, and I don't know what will.

Hope this makes sense; Suggestions greatly appreciated. Many thanks--

[comment]

How about:

Let ( [

alpha = "abcdefghijklmnopqrstuvwxyz" ; 

trailingLetter = Right ( invoiceNumber ; 1 ) ;

trailingLetterAsNumber = Position ( alpha ; trailingLetter ; 1 ; 1 ) ;

subsequentLetter = Middle ( alpha ;  Mod ( trailingLetterAsNumber ; 26 ) + 1 ; 1 )

] ;

Left ( invoiceNumber ; 5 ) & subsequentLetter & " is the next invoice in this series." 

)

Edited April 12, 2008 by Guest
Another typo

[BobWeaver]

Try this:

Let ( [

trailingLetter = Right ( invoiceNumber ; 1 ) ;

trailingLetterAsNumber = Position ( "abcdefghijklmnopqrstuvwxyz" ; trailingLetter ; 1 ; 1 ) ;

trailingLetterPlusOne = trailingLetterAsNumber + 1 ;

subsequentLetter = Middle ("abcdefghijklmnopqrstuvwxyza"; trailingLetterPlusOne );1 ] ;

Left ( invoiceNumber ; 5 ) & subsequentLetter & " is the next invoice in this series." )

You still need to look after the situation where the current number ends in "Z", but this should get you going.

[Saubs]

Thank you so much for your quick replies and useful suggestions, Comment and BobWeaver. Problem solved! Much appreciated.

[BobWeaver]

We aim to please.