Extracting specific numbers from a sequence and converting to year of birth

[katrinthor]

HI,


I need assistance in creating a formula for extracting two specific numbers in a number sequence (in text form) and then converting those numbers to year of birth.


This is what I start with 010474-5589, the 5th and 6th number (74) is the year of birth. The end results would be 1974.


Is this possible? I hope someone out there can assist me.

 

[Marcilio]

Hi Katrinthor.


Try to do this:

Supposing your data are at A1

Use in B1 this formula: VALUE(CONCATENATE("19",MID(A1,5,2)))


Tell us if it worked for you.


I hope it can help you.


Best regards,

Marcilio

------------------------

Belo Horizonte, Brazil

 

[Dhamo]

Hi Katrinthor.


The number is 010474-5589 and the year would be 1974. If I am not wrong, when the number is 010404-5589, It should return 2004.

In that case you can use this.


=IF(MID(A1,5,1)<>"0","19"&MID(A1,5,2),"20"&MID(A1,5,2))


You might need this in future.


Thanks