Here's the formatted text:
You can use the following formula in cell
B1 to extract the day and month of the person's birthdate:
Code:
=IF(LEFT(A1,1)="P","",TEXT(DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)),"dd mmm"))
Let me break down the formula for you:
- The IF function checks if the first character in cell A1 is "P". If it is, it returns an empty string (""), which means it will do nothing.
- If the first character in cell A1 is not "P", it means that the data is in the format of the ID number. The DATE function is used to convert the first 6 digits of the ID number into a date value.
- The TEXT function is used to format the date value into the desired format of "dd mmm". The "dd" represents the day of the month, and the "mmm" represents the abbreviated month name (e.g. Jan, Feb, Mar).
Once you enter this formula into cell
B1, it will automatically extract the day and month of the person's birthdate if the data in cell
A1 is in the format of the ID number. If the data in cell
A1 is in the format of the Passport Number, it will return an empty string.