Finding median of range of cells containing text and numbers

Dear all,

I am attemting to write a formula that can calculate the median of a range of cells containing text and numbers.

More specifically the cells contain the following code: yyyy Qx e.g. 1988 Q2.

I have tried variations on the formula below, but without success.

=MEDIAN(VALUE(LEFT(J16:J53,FIND(" ",J16:J53)-1)))
=VALUE(LEFT(MEDIAN(J16:J53),FIND(" ",MEDIAN(J16:J53)-1)))

Any good ideas??

Thanks a lot!!

I am sorry, here is a sample.

Thanks a lot!

Generally, I would like to do array calculations on that array, as if it was containing numbers only. I would also like to apply the following formula below it, but I cannot figure out how to combine the VAlue(left... formula with the array setup:-)

=SUM(2012-A1:A38)

Would be very glad if anybody had a smart way to do this (without transforming the actual input setup i.e. numbers + text)

Thanks a lot for helping..This is a super forum, I hope I will be good enough at this to help one day!

But I have attached the sample file above??

Sorry, didn't see the expected result request: it is 1994 (the median of the numbers in the cells).

Thanks:-)

Thanks a lot to both of you, they both work perfectly! Also with the other formula:-)

I am sorry, I have one more question, if you don't mind.

I wish to select only certain rows in my column range and have attempted with the formula below (my values are placed in column J instead of column A from the uploaded sample) which however does not work properly. What should I do to only select only certain of the rows in the range?

=MEDIAN(LEFT(J16:J18:J20:J20:J23:J23:J25:J27:J30:J34:J38:J38:J40:J42:J44:J44:J48:J53,4)+0)

Thanks a lot

Thanks a lot, that is an excellent solution!
You have really saved my day (several times). Thanks!