Solved: next alphabetical letter?

[eed]

Hi, all,

This may be painfully obvious, but it's eluding me.

I'm isolating the first letter of a given string using the Mid function. I want to determine the next letter of the alphabet, so I can add that next letter to a separate string.

So, if strOriginal = "Appendix A"
then strLetter = Mid(strOriginal, 10, 1) --> basically, A
and strNextLetter = ... ? --> should result in B

Other than just building a lookup table with the 26 letters of the alphabet and their numeric counterparts 1-26 so I can look up the next letter (which seems tedious), I'm not sure how to do this. Suggestions appreciated!

~ eed

[mvidas]

Hi EED,

What is the next alphabetical letter after Z?
Will you only be working in capital letters, or lower case as well? Does A = a or A <> a ?

If you will never need to know what comes after Z, you could take the Asc() of the character and + 1.strLetter = Mid(strOriginal, 10, 1)
strNextLetter = Chr(Asc(strLetter)+1)Also, if you only need the first letter, why not use Left instead of Mid?
Also, since Asc returns the ascii character code of the first letter of a string, you could technically just usestrNextLetter = Chr(Asc(strOriginal)+1)

However, if A <> a, I would just create an array in the order you want each 'next' letter to fall, something like (in function form):Function NextLetter(ByVal ThisLetter As String) As String
Dim Letters(), i As Long
Letters = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", _
"N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "Aa", "Ab", _
"Ac", "Ad", "etc")
For i = 0 To UBound(Letters)
If ThisLetter = Letters(i) Then
If i = UBound(Letters) Then 'if the match is the last in the list...
NextLetter = Letters(0) 'loop back to the first
Else
NextLetter = Letters(i + 1)
End If
Exit Function
End If
Next
End FunctionMatt

[eed]

Matt,

It isn't technically the first letter, I shouldn't have said "first;" there will always be a word in the string before the letter I want to isolate (either Appendix, Table, or Figure, depending on which case is true in an if/elseif statement). Thus my use of Mid instead of Left.

In answer to your other questions: We can assume that I never need to know the next letter after Z, and all letters will already be CAPS.

I'll try the Asc() function you referenced; that sounds like the kind of thing I was looking for. Otherwise I will probably use an array like your example.

I'll post another reply if I have difficulty... Thanks a bunch!! :thumb

~ eed

[eed]

I'll try the Asc() function you referenced; that sounds like the kind of thing I was looking for.

Yup, that worked like a charm. Thanks for pointing out the fact that this can't go beyond Z: I seriously do not foresee that as being a problem in this context, but I will be sure to check for it, just in case.

... You're my hero!

~ eed

[mvidas]

Glad to help!
If the worst case happens, and you need "Z + 1", you'll get "[" :) Can't hurt thinking of a contingency plan, you never know.
Let me know if you need any more help with this!
Matt

[mdmackillop]

Hi EED,
If you use the Split function, you can simplify your coding. This will return the next letter based on the character after the space; you don't need the character count.


MyText = "Table A"
strNextLetter = Chr(Asc(Split(MyText)(1)) + 1)

[eed]

Hi EED,
If you use the Split function, you can simplify your coding. This will return the next letter based on the character after the space; you don't need the character count.


MyText = "Table A"
strNextLetter = Chr(Asc(Split(MyText)(1)) + 1)



Ooo, thanks for the tip! I'll make a note of that.
~ eed