Google Search

Custom Search

How to use CONCATENATE, YEAR, MONTH, DAY and LEN formulas to create unique IDs


Prerequisite 

Know how to use "CONCATENATE", "YEAR", "MONTH" "LEN" and  "DAY" formulas individually.
Please refer to Microsoft help for Syntax. 

What is concatenation means, please read here, http://en.wikipedia.org/wiki/Concatenation.
Simply put join two or more words.


Possible Usage


To create unique IDs using data code or time stamp. This is more applicable to operation or manufacturing environment.

example: create IDs using dates.

”how

The steps (How to do)


1. type =YEAR(A2) in cell B2

2. type  =MONTH(A2) in cell C2

3. type =DAY(A2) in cell D2

”how

4. Add running numbers in column E. Use "text" format to make sure zeros appear in front.

5. type =concatenate(B2,C2,D2,E2). Drag or copy paste the formula all the way (to cover the all the dates)

”how


Note that the ids are not of the same length. 
To avoid you can use IF and LEN as described below.

2a. type =IF(LEN(MONTH(A2))=1,CONCATENATE("0",MONTH(A2)),MONTH(A2)) in column C 

3a. type =IF(LEN(DAY(A2))=1,CONCATENATE("0",DAY(A2)),DAY(A2)) in Column D

The result will be as follows

”how


Here we go nice and clean IDs.

Done.

All comments are welcome.


No comments:

Post a Comment

 

blogger templates | Make Money Online

Google Analytics Alternative ExpiresDefault access plus 1 year