Excel Formula for Post Code Area

[fion69]

I hope someone can help.  I have a formula to return the first section of a UK Post Code (column C in the attached file) and using this I want to create an IF Statement to assign the related area (Column D in the attached file). 

For example if Column C contains either CA12 , CA13, CA14 etc. then Column D should show West Cumbria and if it contains WA1, WA2 etc. then Column D should show Warrington otherwise it should show Other.  

I have tried various formats for the IF Statement but cannot get the correct values to return - I have tried IF and also IF(OR) but am only getting the value of Other to return.

Any help would be appreciated.

[#2 · Accepted solution]

@fion69 

- you use curvy quotes in formulas which generates and #NAME? error, they are to be changed on "normal" ones

- formula for prefix =IFERROR(LEFT(B2,LEN(B2)-3),"") adds space to it, it shall be =IFERROR(LEFT(B2,LEN(B2)-4),"") or like

[#3]

Thanks so much @Sergei Baklan - works perfectly.:smiling_face_with_smiling_eyes:

[#4]

@fion69 , glad it helped