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 190 , 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 190 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 190 , 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 190 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  
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)  
Seed numbers for random number generation, uniform distribution  Excel Discussion (Misc queries)  
How to Replace numbers and text with numbers only?  Excel Worksheet Functions 