Incrementing an alpha character

  • Thread starter Thread starter Francis Hookham
  • Start date Start date
F

Francis Hookham

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)))
 
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)
 
Many thanks Chip - no problem - the number of increments is unlikely to
exceed half a dozen.

D10-001 etc are door numbers in a schedule - the alpha suffix is only used
when a door is lotted in later.
I just wanted to be able to slot in a few more is necessary. If more than a
few, total renumbering would be justified.

I am most grateful

Francis
 
Francis: Chips code has a few problems. What if the last character is a
number? If you have any problems try this code. It also handles the case
when you get to z it will cause a carry and increment the next character. It
also handles numbers and letters as last character

If IsNumeric(sDNum) Then
sDoorNum = Val(sDNum)
'add 1 to number
sDoorNum = sDoorNum + 1
'format number so it contain leading zeros
sDNum = Format(sDoorNum, "0##")
Else
carry = 1
stringlen = Len(sDNum)
For i = stringlen To (stringlen - 2) Step -1
char = Mid(sDNum, i, 1)
Select Case char
Case "0" To "8"
newchar = Chr(Asc(char) + 1)
carry = 0
Case "9"
newchar = "a"
carry = 0
Case "a" To "y", "A" To "Z"
newchar = Chr(Asc(char) + 1)
carry = 0
Case "z", "Z"
newchar = "0"
carry = 1
End Select
Mid(sDNum, i, 1) = newchar
If carry = 0 Then Exit For
Next i
End If
 
Francis: Chips code has a few problems. What if the last character is a
number?
Click to expand...

Both the formula and VBA versions properly handle numeric input. Both will
increment 101 to 102 and 101-1 to 101-2. I left the 'z' case undefined
because there are any number of things that might be the increment of 'z'.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Now I see what you mean. You're right, the formula is flawed.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Chip,
I must have got something wrong in
sDNum = Left(sDNum, Len(sDNum) - 1) & CHAR(CODE(Right(sDNum, 1)) + 1
+ (6 * (Right(sDNum, 1) = "Z")))

What I do not understand is lowercase 'CHAR(CODE' does not become upper case
as I would expect. So, is there a mistake there.

The code I am trying to get right is below. I am trying to increment door
numbers. Some have a suffix and some not so
D01-012 will change to D01-013
D02-004a will change to D02-004b

Please......

Francis

Sub PrepareForDoor()
'GET LAST DOOR ROW AND NUMBER
iDNRow = Sheets("Store").Cells(1, 1)
sDNum = Cells(iDNRow, 5)
'IS IT AN ORDINARY OR INSERTED DOOR?
If Len(sDNum) = 7 Then
'INCREMENT DOOR NUMBER
sZeroes = "0000000000"
sSuffix = Right(sDNum, Len(sDNum) - InStr(sDNum, "-"))
sDNum = Left(sDNum, InStr(sDNum, "-")) & _
Format(sSuffix + 1, Left(sZeroes, Len(sSuffix)))
sDNumNext = sDNum
Else
'INCREMENT DOOR NUMBER SUFFIX
sDNum = Left(sDNum, Len(sDNum) - 1) & CHAR(CODE(Right(sDNum, 1)) + 1
+ (6 * (Right(sDNum, 1) = "Z")))
sDNumNext = sDNum
End If
'ASK IF THIS IS NEXT DOOR NUMBER
sDNumNext = InputBox("This is next door number" _
& vbNewLine & "following the last door entered." _
& vbNewLine & "Check carefully before clicking 'OK'" _
& vbNewLine & "or change it first if you are" _
& vbNewLine & " inserting a door" _
& vbNewLine & " or" _
& vbNewLine & " starting another floor." _
& vbNewLine, _
"Next door number?", sDNumNext, 2)

'etc
'etc
End Sub
 
Try this code and see if it works better than chips code. chip code doesn't
handle all the possible cases.

Sub PrepareForDoor()
'GET LAST DOOR ROW AND NUMBER
iDNRow = Sheets("Store").Cells(1, 1)
sDNum = Cells(iDNRow, 5)
sDNumNext = sDNum
If IsNumeric(sDNumNext) Then
sDNumNext = Val(sDNumNext)
'add 1 to number
sDNumNext = sDNumNext + 1
'format number so it contain leading zeros
sDNumNext = Format(sDNumNext, "0##")
Else
carry = 1
stringlen = Len(sDNumNext)
For i = stringlen To (stringlen - 2) Step -1
char = Mid(sDNumNext, i, 1)
Select Case char
Case "0" To "8"
newchar = Chr(Asc(char) + 1)
carry = 0
Case "9"
newchar = "a"
carry = 0
Case "a" To "y", "A" To "Z"
newchar = Chr(Asc(char) + 1)
carry = 0
Case "z", "Z"
newchar = "0"
carry = 1
End Select
Mid(sDNumNext, i, 1) = newchar
If carry = 0 Then Exit For
Next i
End If

sDNumNext = InputBox("This is next door number" _
& vbNewLine & "following the last door entered." _
& vbNewLine & "Check carefully before clicking 'OK'" _
& vbNewLine & "or change it first if you are" _
& vbNewLine & " inserting a door" _
& vbNewLine & " or" _
& vbNewLine & " starting another floor." _
& vbNewLine, _
"Next door number?", sDNumNext, 2)

'etc
'etc
End Sub
 
Wow! That's great. Works a treat.

(If you really wanted to put the icing on the cake, I need to work out the
next floor level.
Floor levels are D## followed by a '-' then the door number ###
So ground floor door number 3 is D01-003
In the InputBox I could put a note saying something like 'Backspace to be
left blank to start next door'
If sDNumNext is "" then work out the next floor level and make sDNumNext =
D02-001
I've just got to work out how to increment the ## by 1)

I really am most grateful for what you have done so quickly - in the time it
takes to catch the bus downtown for a flu jab and come home!

Francis
 
The code doesn't need to be changed as long as you set sDNum = suffix part of
the string. This is basically the code I posted during your previous
requests.


Get the prefix from ABC (ABC is a cell or another variable)
pDNum = left(ABC,instr(ABC,"-") - 1)
sDNum = mid(ABC,instr(ABC,"-") + 1)

then run code below which put incremented string into sDNumNext
now put string back together

newstring = pDNum & sDNumNext

I can't tell from this piece of code how to make the complete fix so I gave
you the pieces to do yourself.
 
Back
Top