Francis,
The following formula and VBA function will incrment A - Z - a - z. What
should happen if the last character is a lower case 'z'?
=LEFT(A1,LEN(A1)-1)&CHAR(CODE(RIGHT(A1,1))+1+(6*(RIGHT(A1,1)="Z")) )
Function IncrAlpha(InChars As String) As String
IncrAlpha = Left(InChars, Len(InChars) - 1) & _
Chr(Asc(Right(InChars, 1)) + 1 - (6 * (Right(InChars, 1) = "Z")))
End Function
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Francis Hookham" wrote in message
...
Earlier postings have shown me how to increment a string where the last
characters are numerals so
D01-004
can be incremented to
D01-005
(see code below)
I also need to be able to increment the last character when it is a letter
rather than a number, for instance,
D01-006c
incremented to
D01-006d
Any suggestions?
Francis Hookham
Code used to increment D01-004 to D01-005 is
sZeroes = "0000000000"
sSuffix = Right(sDNum, Len(sDNum) - InStr(sDNum, "-"))
sDNum = Left(sDNum, InStr(sDNum, "-")) & _
Format(sSuffix + 1, Left(sZeroes, Len(sSuffix)))