SOLVED
Home

Continue Pattern (includes letters and numbers)

%3CLINGO-SUB%20id%3D%22lingo-sub-750707%22%20slang%3D%22en-US%22%3EContinue%20Pattern%20(includes%20letters%20and%20numbers)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-750707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20500px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123053iA39770FC577D04C7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI'd%20like%20to%20continue%20this%20pattern%20on%20this%20spreadsheet%2C%20but%20every%20time%20I%20try%20to%20drag%20it%20it%20will%20repeat%20the%20pattern%20(A%2C%20B%2C%20C%2C%20D%2C%20A%2C%20B%2C%20C%2C%20D)%20instead%20of%20continuing%20with%20the%20alphabet%20(A%2C%20B%2C%20C%2C%20D%2C%20E%2C%20F%2C%20G...).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20manually%20typing%20them%20all%20in%2C%20which%20just%20doesn't%20work%20for%20me.%20All%20the%20formulas%20I've%20tried%20looking%20up%20are%20for%20just%20letters%20or%20just%20numbers%2C%20nothing%20that%20includes%20both.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-750707%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-750740%22%20slang%3D%22en-US%22%3ERe%3A%20Continue%20Pattern%20(includes%20letters%20and%20numbers)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-750740%22%20slang%3D%22en-US%22%3ETry%20this%20formula%20in%20A1%20and%20copy%20to%20your%20range%3A%3CBR%20%2F%3E%3DROW()%2B20%26amp%3B%3CBR%20%2F%3ESUBSTITUTE(ADDRESS(1%2C%3CBR%20%2F%3ECOLUMN()%2C4)%2C%3CBR%20%2F%3E1%2C%E2%80%9D%E2%80%9D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-750746%22%20slang%3D%22en-US%22%3ERe%3A%20Continue%20Pattern%20(includes%20letters%20and%20numbers)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-750746%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374941%22%20target%3D%22_blank%22%3E%40katie_lacy%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20put%20this%20formula%20in%20the%20first%20cell%2C%20then%20drag%20it%20to%20the%20right%20and%20all%20the%20way%20down.%3C%2FP%3E%3CPRE%3E%3DROW(A21)%26amp%3BRIGHT(B%241)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123061i91501CF5E5D44396%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot_2.png%22%20title%3D%22Screenshot_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20done%20by%20depending%20on%20the%20letters%20in%20the%20headers%20(Column%20A%2C%20Column%20B%2C%20etc)%20as%20a%20reference%20in%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-750964%22%20slang%3D%22en-US%22%3ERe%3A%20Continue%20Pattern%20(includes%20letters%20and%20numbers)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-750964%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20worked%2C%20thank%20you!%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-750978%22%20slang%3D%22en-US%22%3ERe%3A%20Continue%20Pattern%20(includes%20letters%20and%20numbers)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-750978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374941%22%20target%3D%22_blank%22%3E%40katie_lacy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20macro%20that%20will%20do%20the%20needful%2C%20as%20long%20as%20you%20don't%20need%20more%20than%2026%20letters.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPut%20the%20starting%20value%20in%20the%20first%20cell%2C%20then%20select%20the%20range%20you%20want%20filled%20and%20run%20the%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20FillAlphaSeries()%3CBR%20%2F%3E'Appends%20successive%20alphabetic%20characters%20to%20each%20cell%20in%20a%20selection.%20_%3CBR%20%2F%3EThe%20first%20cell%20in%20selection%20will%20retain%20its%20original%20value.%20Each%20sucessive%20cell%20will%20increment%20the%20rightmost%20character%20code%20by%201.%20_%3CBR%20%2F%3EIn%20so%20doing%2C%20you%20can%20create%20a%20series%20like%2021A%2C%2021B%2C%2021C.%3CBR%20%2F%3EDim%20cel%20As%20Range%2C%20rg%20As%20Range%3CBR%20%2F%3EDim%20root%20As%20String%3CBR%20%2F%3EDim%20i%20As%20Long%3CBR%20%2F%3ESet%20rg%20%3D%20Selection%3CBR%20%2F%3Eroot%20%3D%20rg.Cells(1).Value%3CBR%20%2F%3Ei%20%3D%20Asc(Right(root%2C%201))%3CBR%20%2F%3Eroot%20%3D%20Left(root%2C%20Len(root)%20-%201)%3CBR%20%2F%3EFor%20Each%20cel%20In%20rg.Cells%3CBR%20%2F%3Ecel.Value%20%3D%20root%20%26amp%3B%20Chr(i)%3CBR%20%2F%3Ei%20%3D%20i%20%2B%201%3CBR%20%2F%3ENext%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E
katie_lacy
New Contributor

Capture.JPG

I'd like to continue this pattern on this spreadsheet, but every time I try to drag it it will repeat the pattern (A, B, C, D, A, B, C, D) instead of continuing with the alphabet (A, B, C, D, E, F, G...).

 

I've been manually typing them all in, which just doesn't work for me. All the formulas I've tried looking up are for just letters or just numbers, nothing that includes both.

4 Replies
Try this formula in A1 and copy to your range:
=ROW()+20&
SUBSTITUTE(ADDRESS(1,
COLUMN(),4),
1,””)
Solution

@katie_lacy

 

Hi,

 

Please put this formula in the first cell, then drag it to the right and all the way down.

=ROW(A21)&RIGHT(B$1)

Screenshot_2.png

 

It's done by depending on the letters in the headers (Column A, Column B, etc) as a reference in the formula.

 

Hope that helps

 

That worked, thank you! @Haytham Amairah 

@katie_lacy 

Here is a macro that will do the needful, as long as you don't need more than 26 letters.

 

Put the starting value in the first cell, then select the range you want filled and run the macro.

 

Sub FillAlphaSeries()
'Appends successive alphabetic characters to each cell in a selection. _
The first cell in selection will retain its original value. Each sucessive cell will increment the rightmost character code by 1. _
In so doing, you can create a series like 21A, 21B, 21C.
Dim cel As Range, rg As Range
Dim root As String
Dim i As Long
Set rg = Selection
root = rg.Cells(1).Value
i = Asc(Right(root, 1))
root = Left(root, Len(root) - 1)
For Each cel In rg.Cells
cel.Value = root & Chr(i)
i = i + 1
Next

End Sub

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies