How do I get a series of Letters in excel?
cptn_...@my-deja.com
I can seem to get the series to work. If I start with aaa in the first
cell and then put aab in the next box, then try to drag a series, I
just get aaa, aab, aaa, aab ... I've tried fiddling with the options
but have had no luck. Can I even do this? I have no experience with
VBA, so that's out as well. Any assistance in this matter would be
greatly appreicated.
Thanks!
Jeff Hauser
Sent via Deja.com
http://www.deja.com/
jupiter
In an empty column, put 97 in the first cell, 98 in the next, and so
forth. Assuming these numbers are in column A, then in your desired
column, enter:
="aa"&char(A1)
in the first cell, and then copy down as needed. This works up to
aaz. If you want the sequence to continue with aba, abb, etc., then
we would need to do something a little more complicated.
cptn_...@my-deja.com wrote:
Bernie Deitrick
Since you have no experience in VBA, I won't give you a user-defined
function, which would be much easier to understand. However, put a 0 in
cell A1, a 1 in cell A2, select both, and drag them down until you reach
your reguired length. Then in cell B1, put this formula (it should be all
on one line, without the line breaks or carriage returns that the newsgroup
might put in):
=CHAR((A1-(A1-INT(A1/26)*26)-((A1-(A1-INT(A1/26)*26))/26-INT((A1-(A1-INT(A1/26)*26))/26/26)
*26)*26)/26/26-INT((A1-(A1-INT(A1/26)*26)-((A1-(A1-INT(A1/26)*26))/26-INT((A1-(A1-INT(A1/26)
*26))/26/26)*26)*26)/26/26/26)*26*26+97) & CHAR((A1 - (A1 - INT(A1 / 26) *
26)) / 26 - INT((A1 - (A1 - INT(A1 / 26) * 26)) / 26 / 26) * 26 +97) &
CHAR(A1 - INT(A1 / 26) * 26+97)
and copy it down to match your number sequence in column A. This will work
up to zzz, at which point it will wrap back to aaa.
HTH,
Bernie
Bernie Deitrick
Bernie Deitrick wrote:
> This will work up to zzz, at which point it will wrap back to aaa.
The formula won't wrap back to aaa automatically: you would need to change the 17576 in column A
to a zero and start over. The UDF function DID wrap however.....
Bernie
EarlK
Seems to me I saw a solution to this problem in one of the Excel ng's (I
think). Quite recently. Use your search command, or maybe deja vu (if
they're still doing the ngs).
--
Regards from Virginia Beach,
EarlK
ea...@livenet.net
-------------------------------------------------------------
<cptn_...@my-deja.com> wrote in message
news:9203vi$i5k$1...@nnrp1.deja.com...
Jimmy L. Day
David McRitchie
Bernie Dietrich provided a Worksheet solution in this same thread
http://www.deja.com/=dnc/getdoc.xp?AN=707826228
which can be seen at Deja.
Here is a macro solution that will work with the selection range
that will provide up to 456976 cells AAAA, AAAB, ..., ZZZZ
Sub AAA_ZZZ()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' David McRitchie, 2000-12-30 in excel.misc
' AAA AAB AAC ... ZZZ, AAAD AAAA AAAB AAAC ZZZZ
On Error GoTo done
Dim i As Long, cell As Range
Dim Make_A_Break As Long 'allow change while running
Dim L1 As String, L2 As String, L3 As String
Dim L4 As String, X4 As String
i = 0
For Each cell In Selection
' Chr(65) is the letter A
L1 = Chr(65 + Int(i / 17576) Mod 26)
L2 = Chr(65 + Int(i / 676) Mod 26)
L3 = Chr(65 + Int(i / 26) Mod 26)
L4 = Chr(65 + i Mod 26)
X4 = L1 & L2 & L3 & L4
cell.Value = X4
If i Mod 5200 = 0 Then
Application.StatusBar = Format(cell.Row, "00,000") _
& " " & X4 & " " & Format(i, "00,000,000")
Make_A_Break = DoEvents 'relinguish to system
End If
If i >= 456975 Then GoTo done ' cell 17576 x 26 - 1
i = i + 1
Next cell
done:
beep
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = False
'Also of interest is
'http://www.deja.com/=dnc/getdoc.xp?AN=707826228
'A worksheet solution provided by Bernie Dietrich
' <3A43ACED...@consumer.org> 22 Dec 2000
End Sub
HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Jimmy L. Day <two...@cableone.net> wrote in message
news:3A4AAD5A...@cableone.net...
> This will get you through the first 26 in the series: ="aaa"&A1 in B1 and
>
> EarlK wrote:
> > Seems to me I saw a solution to this problem in one of the Excel ng's (I
> > think). Quite recently. Use your search command, or maybe deja vu (if
> > they're still doing the ngs).
> >
> > <cptn_...@my-deja.com> wrote in message
> > news:9203vi$i5k$1...@nnrp1.deja.com...
> > > I want excel to do a series of letters, ie. aaa, aab, aac, aad ... and
> > > I can seem to get the series to work. If I start with aaa in the first
> > > cell and then put aab in the next box, then try to drag a series, I
> > > just get aaa, aab, aaa, aab ... I've tried fiddling with the options
> > > but have had no luck. Can I even do this? I have no experience with
> > > VBA, so that's out as well. Any assistance in this matter would be
> > > greatly appreicated. Thanks! -- Jeff Hauser
Myrna Larson
wrote:
I'm not too sure how relevant this VBA routine is to the poster's problem, but
here's a modification of some code I worked up a couple of years ago to
increment things like invoice numbers that consist of digits plus letters and
symbols.
The routine adds a positive or negative number, N, to the string.
If the character at a given position is an upper case letter, it will remain
an upper case letter (i.e. Z wraps to A and generates a "carry"); a lower case
letter remains a lower case letter (z wraps to a with a carry); digits remain
as digits; symbols are skipped.
Adding 17 to the string
AB-0a0
gives AB-0b7
The maximum value for that "number" would be ZZ-9z9, and the minimum value
AA-0a0. A string of ##'s indicates overflow or underflow.
For the current problem, put the text "aaaa" in, say, A1, and in A2, the
formula =IncrementString(A1) and copy down.
~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit
Function IncrementString(sText As String, Optional N As Long = 1) As String
Dim Chars() As Byte
Dim i As Long
Dim Carry As Long
Dim X As Long
Dim DigitOrLetter As Boolean
Dim Base_ As Long
Dim ASCIIAdj As Long
Dim Digit As Long
Chars() = sText
i = UBound(Chars()) - 1
Carry = N
Do While i >= 0 And Carry <> 0
X = Chars(i)
DigitOrLetter = True
Select Case X
Case 48 To 57 '0-9
Base_ = 10
ASCIIAdj = 48
Case 65 To 90, 97 To 122 'A-Z, a-z
Base_ = 26
ASCIIAdj = 65 + (X And 32)
Case Else
DigitOrLetter = False
End Select
If DigitOrLetter Then
X = X - ASCIIAdj + Carry
Digit = X Mod Base_
Carry = X \ Base_
If Digit < 0 Then
Digit = Digit + Base_
Carry = Carry - 1
End If
Chars(i) = Digit + ASCIIAdj
End If
i = i - 2
Loop
If Carry <> 0 Then
IncrementString = String$(Len(sText), "#")
Else
IncrementString = Chars()
End If
End Function