Home |
Search |
Today's Posts |
#1
|
|||
|
|||
generating Unique ID numbers for students
hi, i need help i am a college student, i am creating a spreadsheet for my portfolio i would like to generate ID numbers for students in a spreadsheet i am doing. so what i want to do is take the first 3 letter of a students name then add 00 ,and then it should , generate numbers from 1-90 , than i could use this as an id number. e.g *Dav*id DAV0056 "dav" in the first part is from the name, the two zero are just put in and then i want numbers from 1-90 to be inputed. i have 90 students names that i need to create Unique ID numbers. so please can you tell me the formula/function on how i can do this. thank you for your help in advance.. :) -- Maltenrazer ------------------------------------------------------------------------ Maltenrazer's Profile: http://www.excelforum.com/member.php...o&userid=16130 View this thread: http://www.excelforum.com/showthread...hreadid=275688 |
#2
|
|||
|
|||
Let's suppose your names are in column A.
In B1, enter: =LEFT(A1,3) and fill down In C2, enter: =IF(LEFT(A2,3)=B1,C1+1,0) and fill down In D2, enter: =B2&"00"&TEXT(C2,"00") and fill down -- Kind Regards, Niek Otten Microsoft MVP - Excel "Maltenrazer" wrote in message ... hi, i need help i am a college student, i am creating a spreadsheet for my portfolio i would like to generate ID numbers for students in a spreadsheet i am doing. so what i want to do is take the first 3 letter of a students name then add 00 ,and then it should , generate numbers from 1-90 , than i could use this as an id number. e.g *Dav*id DAV0056 "dav" in the first part is from the name, the two zero are just put in and then i want numbers from 1-90 to be inputed. i have 90 students names that i need to create Unique ID numbers. so please can you tell me the formula/function on how i can do this. thank you for your help in advance.. :) -- Maltenrazer ------------------------------------------------------------------------ Maltenrazer's Profile: http://www.excelforum.com/member.php...o&userid=16130 View this thread: http://www.excelforum.com/showthread...hreadid=275688 |
#3
|
|||
|
|||
Niek Otten wrote... Let's suppose your names are in column A. In B1, enter: =LEFT(A1,3) and fill down In C2, enter: =IF(LEFT(A2,3)=B1,C1+1,0) and fill down In D2, enter: =B2&"00"&TEXT(C2,"00") and fill down ... Um, why not just enter D2: =LEFT(A2,3)&TEXT(ROW()-1,"0000") and fill down with no intermediate formulas in columns B or C. This assumes OP wants the serial number portion of the IDs not to reset when the partial name protion changes. If s/he does want resetting to 0001 rather than 0000, then D2: =LEFT(A2,3)&"0001" D3: =LEFT(A3,3)&TEXT(IF(LEFT(A3,3)=LEFT(A2,3),RIGHT(D2 ,4)+1,1),"0000") and fill D3 down. Note that these latter formulas assume the names in column A are sorted. Note also that this entire exercise is pointless. If the sole purpose is creating unique indentifiers, then using formulas like D2: =TEXT(ROW()-1,"0000") would be sufficient. Many (most?) of the nastier referential integrity problems stem from the misguided desire to make such identifiers 'friendly'. Why bother? -- hgrove ------------------------------------------------------------------------ hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432 View this thread: http://www.excelforum.com/showthread...hreadid=275688 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
How to Replace numbers and text with numbers only? | Excel Worksheet Functions |