Incrementing an alpha character

[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)))

 

[Chip Pearson]

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]

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

 

[Joel]

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

 

[Chip Pearson]

Francis: Chips code has a few problems. What if the last character is a

number?

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)

 

[Joel]

Chip: what does your code do with "0D9". Does it make it "0D:" ?

 

[Chip Pearson]

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)

 

[Francis Hookham]

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

 

[Joel]

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

 

[Francis Hookham]

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

 

[Joel]

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.

 

[Francis Hookham]

Thank very much - I am sure I shall be able to do that.

Francis