Do you want the Value Jan07 in that cell or do you want a date in that cell and
format it so that it shows Jan07?

With activesheet.cells(2,2)
 if .value = 20070101 then
     .numberformat = "@" 'text
     .value = "Jan07"
 end if
end with

or maybe...

With activesheet.cells(2,2)
 .value = dateserial(left(.value,4),month(mid(.value,5,2), day(mid(.value,7,2))
 .numberformat = "mmmyy"
end with

=====
If you have a column of this kind of number and want to convert that whole
column to a real date:

Select the single column range
data|text to columns
Fixed width (but remove any lines that excel guessed)
Choose date (ymd or ydm???)
and finish up

Then format that range the way you want.