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.

 

[Andrew Poulsom]

Welcome to MrExcel.

To extract the letters from the beginning of your postcode:

=LEFT(A1,1+NOT(ISNUMBER(MID(A1,2,1)+0)))

 

[markmzz]

Or try this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Lookup</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>AB</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>A</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>S</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>PostCode</TD><TD>Result</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>AB5 4RG</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>A33 6RG</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>S70 2EE</TD><TD style="TEXT-ALIGN: right">2</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B7</TH><TD style="TEXT-ALIGN: left">=VLOOKUP(LEFT(A7,MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},A7&"0123456789"))-1),$A$2:$B$4,2,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

 

[testsubjec]

Thanks for the quick reply guys.

Just to add on the back of this, is there a check which allows me to do the following:

If Cell A1 Fill Colour = #F2DCDB then "A Service" else "N/A"

Thanks

 

[Andrew Poulsom]

What's #F2DCDB? There are some custom functions for colours here:

http://www.cpearson.com/Excel/colors.aspx

 

[testsubjec]

Thanks Andrew. #F2DCDB is the Fill Colour of the cell I'm trying to pick out.

One final question

If the Post Code is:

B4 7GH
PL32 9ER
TN12

How could I extract the first part of the Post Code before the space.

Example:

B4 7GH = B4
PL32 9ER = PL32
TN12 = TN12

Thanks again.

 

[Andrew Poulsom]

Like this:

=LEFT(A1,FIND(" ",A1)-1)

 

[testsubjec]

Thanks again Andrew.

Iv'e stumbled into another problem and would like to know if there is an easier way to do this.

Below is a screen shot of the spreadsheet I'm using.

What I am trying to do is work out the Zone for each PostCode from a lookup table (which is working fine) and then depending on which Zone the job falls into, it then decides which lookup table it use, to get the costings for the job.

Below I've put a screen shot of one of the Zone Costings lookup table.

The formula I have been using is below:

=IF(O10="Zone 1",VLOOKUP(I10,'OLD NEXT DAY ZONING MATRIX'!$E$5:$F$14,2)

But the problem is you can only nest so many IF's and I've hit that limit.

Is there a much simpler way of doing this??

Thanks again

 

[GlennUK]

... But the problem is you can only nest so many IF's and I've hit that limit.

Show the formula that you've got that's hit the limit ... or even better show the formula as you would have it if there was no limit.

 

[Andrew Poulsom]

If you name your ranges Zone 1, Zone 2 etc you can use:

=VLOOKUP(I10,INDIRECT(O10),2)

Depending on how your lookup tables are laid out there may be another way using INDEX.