For some reasons, you have an Excel sheet with a bunch of Malaysian IC numbers and you would like to convert them into birthdays for your CRM campaigns and etc. Here is an easy way for you to do it easily. By using Excel functions.

Let’s assume that the IC numbers are in the following format.

670607-88-8888

The birthday format needed by you is as below.

1967-06-07

The steps are simple.

1. Enter the following formula to the birthday column.

**=CONCATENATE(“19″,LEFT(C2,2),”-“,MID(C2,3,2),”-“,MID(C2,5,2))**

C2 is the column and the row where the IC number reside.The formula above will ad 19 in front of the year and put “-” as the separators of year, month and day. If the IC that you have involves people born after year 1999 then you are out of luck.

2. Copy the formula above to the rest of the remaining birthday cells through dragging or good old copy and paste.

Does this help you? Do let me know if it helps as it is the source of encouragement for me to write more.

Edit :

24/5/2014 – I’ve corrected the error in the original formula. If this new formula still gives you error, please try and retype all the ” symbols in the formula in your excel. It should solve your problem. It is a problem of conversion from web characters to excel characters.

Icon by iconmonstr