If part of a cell = "NVA" then insert "Norfolk" in return cell

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?

"RNCKWMN" wrote:
What formula or function do i use to make this happen?

=lookup(mid(C1,2,3), {"NVA","RVA","SNC"}, {"Norfolk","Richmond","Selma"})

That makes it simple to extend to additional codes. However, if need to
cover "none of the above", post back for additional assistance, specifying
your version of Excel.


----- original message -----

"RNCKWMN" wrote in message
...
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?

Judging by the third example "Selma" I would guess you have many more places
than just Norfolk and Richmond.

=MID(C2,2,3)
gives you the NVA, RVA, SNC

You can have some extra columns, let's say X and Y, where you will have the
correspondence between NVA and Norfol etc:

Col X Col Y
NVA Norfolk
RVA Richmond
SNC Selma
etc

So what you need in column J will be:
=VLOOKUP(MID(C2,2,3),X:Y,2,0)

--
Allllen


"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?

Hi,

Suppose you create a little lookup table in L1:M3:

NVA Norfolk
RVA Richmond
SNC Selma

then your formula in J1 would be

=LOOKUP(MID(C1,2,3),L$1:M$3)

If you named the range L1:M3 "T" then this would be

=LOOKUP(MID(C1,2,3),T)
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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?

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?