Number and text in same cell

Question

In cell A1 is =Today() In cell A2 is =365-(DATE(YEAR(A1),12,31)-A1) This returns the number of days in the year so far.

I want to add the last two digits of the CURRENT year to this for example

If using 11/17/2010 as an example A1 displays 11/17/2010 A2 displays 321 I want A2 to display 32110 or if I do two batches that day then 32110A

everything I try gives me #VALUE?

Thank You"

Answer

Assume that your date's are in Column-A and the expected result is Column-B and the first row is having the column headers (Column-A : Date; Column - B : Result) like the below.

Column-A Date 17-Nov-10 5-Nov-10 16-Nov-10 17-Nov-10 16-Nov-10 15-Oct-10 10-Oct-10 15-Nov-10 15-Oct-10 17-Nov-10 16-Nov-10 17-Nov-12

Column-B Result

Copy and paste the below formula in B2 cell =IF(COUNTIF(A:A,A2)=1,IF((YEAR(A2)/4)-INT(YEAR(A2)/4)=0,366,365)-(DATE(YEAR(A2),12,31)-A2)&TEXT(A2,"YY"),SUBSTITUTE(IF((YEAR(A2)/4)-INT(YEAR(A2)/4)=0,366,365)-(DATE(YEAR(A2),12,31)-A2)&TEXT(A2,"YY")&"-","000-","")&IF(ISNUMBER(CHAR(MAX(CODE(MID($B$1:B1,FIND("-",$B$1:B1)+1,255))))),CHAR(MAX(CODE(MID($B$1:B1,FIND("-",$B$1:B1)+1,255)))),IF(ISNUMBER(A2),CHAR(64+COUNTIF($A$1:A2,A2)),"")))

Drag the B2 cell formula to the remaining cells of B column based on the Column-A data.

For your easy reference I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file.

http://www.sendspace.com/file/2l34rq

Scroll the mouse button to the bottom of the website and click Download

The File Name is: Number & Text In Same Cell.xls

Hope it's Clear to you!