alphanumeric counter

[jadams0173]

I'm trying to figure out a way to increment an alphanumeric variable. I'm having difficulty getting the letters to increment properly. I have something like VF-R1-S1A and need to increment it under cerain circumstnaces to VF-R1-S1B. Any suggestions or recomendations?

 

[Tom951]

The rightmost "A" has a numeric value of 65. If you add one, it is 66, which is equivalent to "B", and so on. After "Z", you will get "[\]^_`", and then start on the lowercase characters beginning with "a". You have to decide what to do about that.

So... you can pick off the rightmost character with the Right() or Mid() function. Use Len() to get the length if needed. Convert the character to its numeric value with ASC(), increment it, check for rollover, and convert it back to a character with Chr() function. Use Mid() to remove the old trailing character from your string and append the new.

Get it?

Tom

 

[jadams0173]

Got it. Just needed a jump start. Thanks for the reply!!

 

[SkipVought]

If you use the MOD function in conjunction with the 65 value, you can get the value to "Wrap"

=CHR(65+Counter MOD 26)

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[SkipVought]

FYI thread707-1119392 give a 3-character solution in Excel which can easily be modified for your situation.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]