Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to autofill strings
Ok, autofill is used to sequence numbers in a row or column. How do I
autofill a column with a string sequence starting with 3 cells such as: AL0176A AL0176B AL0176C Ideally would want the result of values AL0176D AL0176E AL0176F, etc. I hope my meaning is clear! :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to autofill strings
Try this:
A10: ="AL0176"&CHAR(65+ROW()-10) Copy down as far as needed If you start the series in another cell...change the 10 to the row number of the first cell of the series. Example: if the series starts on B5, then B5:="AL0176"&CHAR(65+ROW()-5) If you need "hardcoded" values, copy the range then PasteSpecialValues Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Allewyn" wrote: Ok, autofill is used to sequence numbers in a row or column. How do I autofill a column with a string sequence starting with 3 cells such as: AL0176A AL0176B AL0176C Ideally would want the result of values AL0176D AL0176E AL0176F, etc. I hope my meaning is clear! :) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to autofill strings
Ok, but where do I type that, in the cell B5? There's no place to type it in
the pastespecialvalues dropdown "Ron Coderre" wrote: Try this: A10: ="AL0176"&CHAR(65+ROW()-10) Copy down as far as needed If you start the series in another cell...change the 10 to the row number of the first cell of the series. Example: if the series starts on B5, then B5:="AL0176"&CHAR(65+ROW()-5) If you need "hardcoded" values, copy the range then PasteSpecialValues Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Allewyn" wrote: Ok, autofill is used to sequence numbers in a row or column. How do I autofill a column with a string sequence starting with 3 cells such as: AL0176A AL0176B AL0176C Ideally would want the result of values AL0176D AL0176E AL0176F, etc. I hope my meaning is clear! :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to autofill strings
Can't do it with autofill, but you could use a formula, the general form
of which would be: =LEFT(A1,6)&CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65) In article , "Allewyn" wrote: Ok, autofill is used to sequence numbers in a row or column. How do I autofill a column with a string sequence starting with 3 cells such as: AL0176A AL0176B AL0176C Ideally would want the result of values AL0176D AL0176E AL0176F, etc. I hope my meaning is clear! :) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to autofill strings
First, you put the formula in the first cell and press [Enter].
Second, you copy that formula down as far as you need it. Next, you select from the first list cell through the last list cell. Then, EditCopy. Finally, EditPaste SpecialValues Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Allewyn" wrote: Ok, but where do I type that, in the cell B5? There's no place to type it in the pastespecialvalues dropdown "Ron Coderre" wrote: Try this: A10: ="AL0176"&CHAR(65+ROW()-10) Copy down as far as needed If you start the series in another cell...change the 10 to the row number of the first cell of the series. Example: if the series starts on B5, then B5:="AL0176"&CHAR(65+ROW()-5) If you need "hardcoded" values, copy the range then PasteSpecialValues Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Allewyn" wrote: Ok, autofill is used to sequence numbers in a row or column. How do I autofill a column with a string sequence starting with 3 cells such as: AL0176A AL0176B AL0176C Ideally would want the result of values AL0176D AL0176E AL0176F, etc. I hope my meaning is clear! :) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to autofill strings
Oops, pasted the wrong formula in - this one will increment the numeric
digits when the right-most character gets to "Z": =LEFT(A1,2) & TEXT(MID(A1,3,4)+(RIGHT(A1,1)="Z"),"0000") & CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65) Copy down as far as necessary. In article , JE McGimpsey wrote: Can't do it with autofill, but you could use a formula, the general form of which would be: =LEFT(A1,6)&CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to autofill strings
OK, I'm not understanding this but here's what I did:
al0176a Al0176b al0176c al0176d al0176H al0176e Al0176I al0176f al0176J al0176g al0176K al0176L al0176M al0176N #VALUE! #VALUE! #VALUE! I originally had only al0176a - c maunally typed. Then I pasted the formula into cell b4 and dragged it down 9 cells, with the result of al0176H in cell b4 and #value! in the 9 cells under it. Then I experimented by typing in al0176d in column A and noticed the other column (cell b5) changed to al0176I. I then typed al0176E in the next cell down of coulmn A and noticed a change to al0176J in b6. Each time I typed a vlue into caolumn A, the next cell down changed to the next value sequentially. If I haven't completely garbled this communication, (heh) is this what should happen? Main question now: why did b4 receive the value "H" when the last typed value was "C"? Is there a place to read up on this? "JE McGimpsey" wrote: Oops, pasted the wrong formula in - this one will increment the numeric digits when the right-most character gets to "Z": =LEFT(A1,2) & TEXT(MID(A1,3,4)+(RIGHT(A1,1)="Z"),"0000") & CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65) Copy down as far as necessary. In article , JE McGimpsey wrote: Can't do it with autofill, but you could use a formula, the general form of which would be: =LEFT(A1,6)&CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi-sheet autofill | Excel Discussion (Misc queries) | |||
autofill | Excel Discussion (Misc queries) | |||
More- AutoFill with Non-Seqeuntial Cell References ? | Excel Worksheet Functions | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Relative reference autofill increment other than +1 | Excel Discussion (Misc queries) |