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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]animal01[/td][td]Dog[/td][td]Male[/td][td]

07-Feb-19​

[/td][td=bgcolor:#E2EFDA]DM219[/td][td] =LEFT(B1,1)&LEFT(C1,1)&MONTH(D1)&RIGHT(YEAR(D1),2)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]animal02[/td][td]Cat[/td][td]Female[/td][td]

08-Mar-19​

[/td][td=bgcolor:#E2EFDA]CF319[/td][td] =LEFT(B2,1)&LEFT(C2,1)&MONTH(D2)&RIGHT(YEAR(D2),2)[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]animal01[/TD]
[TD]Dog[/TD]
[TD]Male[/TD]
[TD]

07-Feb-19​

[/TD]
[TD="bgcolor: #E2EFDA"]DM219[/TD]
[TD] =LEFT(B1,1)&LEFT(C1,1)&MONTH(D1)&RIGHT(YEAR(D1),2)[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]animal02[/TD]
[TD]Cat[/TD]
[TD]Female[/TD]
[TD]

08-Mar-19​

[/TD]
[TD="bgcolor: #E2EFDA"]CF319[/TD]
[TD] =LEFT(B2,1)&LEFT(C2,1)&MONTH(D2)&RIGHT(YEAR(D2),2)[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

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?