Incrementing letters (just a quick q)

[Mightyginger]

Is it possible to increment letters? (i.e. an easy way). Oh, and I really need to go from z to A once it reaches the end and from Z to a. Is there an easy way of doing this?


Thanks guys,



Neil.

 

[SmallCraig]

Neil,

To "increment" letters, you need to use the ASCII values of the letters, and increment those, converting them back to letters to use them.

The ASCII value of "A" is 65, "a" is 97, etc.

So, to get the "number" associated with a letter, use the function "Asc":

Dim intLetter As Integer
Dim strLetter as String

strLetter = "A"
intLetter = Asc(strLetter)     ' Returns 65

intLetter = 97
strLetter = Chr(intLetter)     ' Returns "a"

I don't have an ASCII table with me, but if you can't find one somewhere on the web, I'll eat my hat.

Once you know the ASCII values of the letters, and the 2 functions to get letters from the number and vice-versa, it's just a matter of using a simple for-loop, incrementing by 1 or -1, depending on the direction you want to go. The sequence of ASCII values isn't a proper numerical progression, so you'll have to "skip" a few ASCII values between "Z" and "a". Values 91 - 96 are in fact special characters.

There are a couple of examples in the Excel VBA help file (that's where I got mine from).

Hope this helps,

Best of,
SmallCraig

 

[TonyJollans]

Hi Neil,

AFAIK there is no builtin functionality so you need to write your own. One way would be ..

Function NextLetter(Letter As String) As String
NextLetter = IIf(Letter = "Z", "a", IIf(Letter = "z", "A", Chr(Asc(Letter) + 1)))
End Function

Enjoy,
Tony

 

[Mightyginger]

Thanks,

Sorry if I asked an obvious question though.


Really appreciated.



Neil.

 

[xlbo]

IMHO, the REAllY easy way to do this is to go Tools>CustomLists and add a new list which goes from A>Z and then back again

Then, you just need to put
a
in a cell and drag to the left or down and it'll increment your list for you....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244