Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Renaming Sequential Rows to create serial numbers
I am new to Excel.
I have the following cells in one column in sequential rows: aas aas aas aas aas bbc bbc bbc bbc bbc I want to make a command to change these to: aas001 aas002 aas003 aas004 aas005 bbc101 bbc102 bbc103 bbc104 bbc105 How do I change or rename a series of cells in a column, increasing the digit by 1 while retaining the three letter prefix? I'm trying to create product serial numbers in a worksheet with hundreds of rows of products. Thanks, Fred |
#2
|
|||
|
|||
Renaming Sequential Rows to create serial numbers
I assumed your codes starting with a added a 101, 102, etc, and those
starting with b got a 201, 202, etc. I put a table in f5:g6 with a in f5, b in f6, and 1 in g5, 2 in g6 I then listed your 3 letter codes in a4 and down, and put the following in b4 and down. It takes the code in a4, and if the first letter changes (say a to b) it looks up the new first letter and adds the corresponding digit, and a "01". if the first letter does not change, it incrments the digits by 1 =A4&VLOOKUP(LEFT(A4,1),$F$5:$G$6,2)&IF(LEFT(A4,1)< LEFT(A3,1),"01",IF(LEFT(RIGHT(B3,2),1)="0",0,"")& RIGHT(B3,2)+1) not sure if this is what you are looking for "forceten32" wrote: I am new to Excel. I have the following cells in one column in sequential rows: aas aas aas aas aas bbc bbc bbc bbc bbc I want to make a command to change these to: aas001 aas002 aas003 aas004 aas005 bbc101 bbc102 bbc103 bbc104 bbc105 How do I change or rename a series of cells in a column, increasing the digit by 1 while retaining the three letter prefix? I'm trying to create product serial numbers in a worksheet with hundreds of rows of products. Thanks, Fred |
#3
|
|||
|
|||
Renaming Sequential Rows to create serial numbers
"forceten32" wrote:
.. How do I change or rename a series of cells in a column, increasing the digit by 1 while retaining the three letter prefix? I'm trying to create product serial numbers in a worksheet with hundreds of rows of products. One way which might suffice for the intent indicated .. Assuming data in A1 down Put in B1, copy down: =A1&TEXT(COUNTIF($A$1:A1,A1),"000") This would yield in col B, for the sample data posted: aas001 aas002 aas003 aas004 aas005 bbc001 bbc002 bbc003 bbc004 bbc005 etc -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#4
|
|||
|
|||
Renaming Sequential Rows to create serial numbers
Excellent, guys.
Thanks. Fred "Max" wrote in message ... "forceten32" wrote: .. How do I change or rename a series of cells in a column, increasing the digit by 1 while retaining the three letter prefix? I'm trying to create product serial numbers in a worksheet with hundreds of rows of products. One way which might suffice for the intent indicated .. Assuming data in A1 down Put in B1, copy down: =A1&TEXT(COUNTIF($A$1:A1,A1),"000") This would yield in col B, for the sample data posted: aas001 aas002 aas003 aas004 aas005 bbc001 bbc002 bbc003 bbc004 bbc005 etc -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#5
|
|||
|
|||
Renaming Sequential Rows to create serial numbers
You're welcome, Fred
Thanks for the feedback -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "forceten32" wrote in message ink.net... Excellent, guys. Thanks. Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reversing numbers to create dates | Excel Worksheet Functions | |||
How do I create a form template that will be sequential numbered | Excel Discussion (Misc queries) | |||
How do I create a list of sequential numbers using Excel or Acces. | Excel Discussion (Misc queries) | |||
How can i print sequential page numbers 1 to 10, if i have one wo. | Excel Discussion (Misc queries) | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) |