If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Sorting when some numbers have a text suffix  confused on the tundra  Excel Discussion (Misc queries)  5  December 18th 04 11:19 PM 
Sorting imported "numbers"  Confused on the tundra  Excel Discussion (Misc queries)  5  December 17th 04 08:33 PM 
Paste rows of numbers from Word into single Excel cell  BecG  Excel Discussion (Misc queries)  1  December 8th 04 05:55 PM 
Seed numbers for random number generation, uniform distribution  darebo  Excel Discussion (Misc queries)  2  December 6th 04 06:01 PM 
How to Replace numbers and text with numbers only?  Robert Judge  Excel Worksheet Functions  3  November 5th 04 05:36 PM 