Number and text in same cell

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:49 PM GMT


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"


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.

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!


©2020 eLuminary LLC. All rights reserved.