Explanation
The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as follows:
1. The year value is extracted with the RIGHT function:
RIGHT(B5,2)+2000
RIGHT gets the right-most 2 characters from the original value. The number 2000 is added to the result to create a valid year. This number goes into DATE as the year argument.
2. The month value is extracted with:
MID(B5,4,2)
MID retrieves characters 4-5. The result goes into DATE as the month argument.
3. The day value is extracted with:
LEFT(B5,2)
LEFT grabs the final 2 characters of the original text value, which goes into DATE as the day argument.
4. The three values extracted above go into DATE like this:
=DATE(2016,"02","29")
Although month and day are supplied as text, the DATE function automatically converts to numbers and returns a valid date.
Note: the year value 2016 was automatically converted to a number when 2000 was added.
Dealing with extra space
If the original text value contains extra leading or trailing space characters, you can add the TRIM function to remove:
=DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),4,2),LEFT(TRIM(A1),2))