Excel Unique Code

[Alysiin P]

Please I will like to create a formula which automatically generates a code base on the information in the cell entered this is a list of animals. I would like the code to have the type (D) for dog, sex(F), month June(6) and or year 2019 (19) of birth. = DF619.

I will apprericate any help to this regard.

 

[Yongle]

welcome to the forum

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​
1​	animal01	Dog	Male	07-Feb-19​	DM219	=LEFT(B1,1)&LEFT(C1,1)&MONTH(D1)&RIGHT(YEAR(D1),2)
2​	animal02	Cat	Female	08-Mar-19​	CF319	=LEFT(B2,1)&LEFT(C2,1)&MONTH(D2)&RIGHT(YEAR(D2),2)

Sheet: Sheet1

 

[DanteAmor]

Try this

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /><col style="width:103px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">Animal Name</td><td style="background-color:#ffff00; ">Animal Type</td><td style="background-color:#ffff00; ">Sex</td><td style="background-color:#ffff00; ">Date</td><td style="background-color:#ffff00; ">Unique Code</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >cc</td><td >Dog</td><td >Male</td><td style="text-align:right; ">15/01/2019</td><td style="text-align:center; ">DM0119</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >rr</td><td >Cat</td><td >Female</td><td style="text-align:right; ">28/10/2018</td><td style="text-align:center; ">CF1018</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=LEFT(B2,1)&LEFT(C2,1)&TEXT(D2,"mmyy")</td></tr></table></td></tr></table> <br /><br />

 

[Alysiin P]

welcome to the forum

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	animal01	Dog	Male	07-Feb-19​	DM219	=LEFT(B1,1)&LEFT(C1,1)&MONTH(D1)&RIGHT(YEAR(D1),2)
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	animal02	Cat	Female	08-Mar-19​	CF319	=LEFT(B2,1)&LEFT(C2,1)&MONTH(D2)&RIGHT(YEAR(D2),2)

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Thanks Formoula works just fine. Wow

 

[Alysiin P]

Thanks but the formula gives me an err
or

 

[DanteAmor]

What mistake does it make you? What data do you have and how did you put the formula?