assuming you start at row 1 with no header. try this in cell J1
=IF(MID(C1,2,3)="NVA","Norfolk",IF(MID(C1,2,3)="RV A","Richmond",IF(MID(C1,2,3)="SNC","Selma","not in list")))

"RNCKWMN" wrote:

Column C has various 8 character codes (city & state abbreviations). One #
and 7 alpha. If the 2nd, 3rd & 4th characters = "NVA" then I want it to
return "Norfolk" in a Column J... if they = "RVA" it should return "Richmond"

Column C Column J
0NVARIVA should yield Norfolk
2RVACFVA should yield Richmond
1SNCEUNC should yield Selma

What formula or function do i use to make this happen?