Extracting Letters from a String

[testsubjec]

Hi All,

I have a rather large excel spreadsheet which is given to me on a weekly basis. The spreadsheet contains information about job deliveries.

I need to be able to either extract or use a lookup to check part of the Post Code.

Example.

Lookup
AB = £5
A = £2
S = £2

PostCode
AB5 4RG
A33 6RG
S70 2EE

So I need Excel to be able to pick out AB, A and S and then place the relavent charge into another colunm. Now the lookup I can setup without a problem but I am not 100% sure how get Excel to pick out the first couple of letters.

Any help would be much appreicated.

 

[testsubjec]

Ok, just to help matters, I have put a copy of the spreadhseet for you to see exactly what I am trying to do.

http://www.expectdistribution.com/KCImages/Zoning.xls

Thanks

 

[GlennUK]

See Andrew's reply about using INDIRECT.

 

[testsubjec]

Thanks Glenn.

I've never used that function before, could you provide an example??

Thanks again.

 

[GlennUK]

In sheet OLD NEXT DAY ZONING MATRIX

Name E5:F14 as Zone1

Name H5:I14 as Zone2

etc etc

formula in P7 of Sheet1:
=VLOOKUP(I7,INDIRECT(SUBSTITUTE(O7," ","")),2)

 

[testsubjec]

Thank you for all your help with my little problems.

I have just one more little query.

PostCode PL, W, etc are full PostCodes which are assigned a Zone. It has now come to light that we have split PostCodes so for example, B1 - B59 might be assigned Zone 1, B60 - B61 assigned Zone 4, B62 and onwards Zone 9.

Now I can setup the PostCode to Zone lookup table to include these split Postcodes:

B1 - Zone 1
B2 - Zone 1
etc, etc

That's not a problem, if there is a better way to be this then great.

The other other I have is how to get B1 extracted from the Postcode so the vlookup can use this, but for full PostCodes PL, W, etc to use the letters.

Hope this makes sense.

Thanks again for all your help.