Creating Student IDs from student information

Josiah670

New Member
Joined
Aug 20, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
One of the employees at work asked me for help with an excel issue. She has a large list of students in an excel sheet and she needs to create unique IDs using the first three letters of the last name and and the first letter of the first name and finally the Date of birth in the format ddmmyy. I am not great with excel but tried to help. Below you can see a sample without the IDs.

Laura 1.png


After reading some information online, I cam up with this formula:
=UPPER(CONCATENATE(LEFT(D2,3)&(LEFT(C2,1))&TEXT(E2,"ddmmyy")))

There are 2 different issues. All dates starting in single digits 01-09 have the day and month reversed. Also all dates starting from 10 and up will not convert and appear as xx-xx-xxxx, The dates seem to be the main issue and since these excel sheets are large and created by someone else we cannot change them. Am I doing something wrong? Any help would be greatly appreciated. Below you can see the results of the formula.



P.S. Someone in our financial department suggested the following formula but I could not get it to work at all.


=UPPER(CONCATENATE(LEFT([@[Last Name]],3)&(LEFT([@[First Name]],1))&TEXT([@[Date of Birth]],"ddmmyy")))


Any help would be greatly appreciated. Have a great day.



Laura 2.png
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
664
Office Version
  1. 365
Platform
  1. Windows
You need to make that table an actual table, it is using Structured References.

Snag_103350c4.png


 

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
And what if they have same day of birth, and names as in two first rows in your example. How to differ those?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
It sounds as though some of your dates are text, rather than real dates. You will need to convert them to real dates.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,951
Members
410,713
Latest member
TaremyLunsil
Top