Help needed from excel expert

[shekhor]

Hello Everybody,

Currently I am working with a spreadsheet where I have to collect addresses of UK business Directors. Some of the directors have multiple addresses. UK zip code consist of two segment and I have to ignore the address where the first segment of zip code starts with W1 , SW1, EC1, EC2, EC3 & EC4 and ends with character not number because those are geneally industrial address. For example Please check the following addresses where the addresses starts with W1 , SW1, EC1, EC2, EC3 & EC4 and ends with letter and I have to ignore collecting those.

3RD FLOOR 207 REGENT STREET, LONDON, W1B 3HH
42, CHARTERHOUSE SQUARE, LONDON EC1, EC1M 6EU
5, WESTMINSTER GARDENS, LONDON, MARSHAM STREET, SW1P 4JA
160, QUEEN VICTORIA STREET, LONDON, EC4V 4QQ
THE BROADGATE TOWER 20, PRIMROSE STREET, LONDON, EC2A 2RS
BAKERS' HALL 9, HARP LANE, LONDON, EC3R 6DP

Also please take note the zip codes of W11,W12 W13 and so on are not prohibited and same thing applies for the SW1, EC1, EC2, EC3 & EC4. Now as we are working on the bulk data, its impossible to notice the zip codes when collecting the address. So I have tried with combination of various formula to highlight the first segment of those mentioned zip codes in a separate column using just right after pasting the address. In this way I can see the prohibited zip codes right after pasting the address. Although I am not successful cracking on it but I was close of it. I am just sharing the thing I have tried and looking for a compact solution from you guys. First of I have created a separate column E where it will show the first segment of the zip code when I will paste the addresses in the column D. Here is the code which I have used: =TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(D2)," ",REPT(" ",60)),120),60)) Next I have tried to show only those values which starts with W1 , SW1, EC1, EC2, EC3 & EC4 and ends with a letter. So I have created another column F and create the formula for "SW1" =IF(NOT(ISNUMBER(VALUE(RIGHT(K3,1)))), IF(ISNUMBER(SEARCH("SW1",K3)), LEFT(K3,3), ""),"") So if I have to check for W1 , EC1, EC2, EC3 & EC4, I have to create 5 more columns with the same formula where just have to change the value of search function. This lead me to 6 extra columns and I want a compact formula for savings the space because I generally split the browser and execl in a way that's why I can copy and paste data on the spreadsheet without minimizing the spreadsheet. This saves me a lots of time. But creating six more column will make my work more time consuming as I have to check all six columns for those zip codes. Question - 1: I want to ask, is there any way to make a compact formula for showing my desired result in a single column only? Question - 2: We also have to ignore the addresses which consist the words "floor","house" & "airport". I have tried the below formula for single query: =IF(ISNUMBER(SEARCH("Floor",D2)), "Floor", "") Is there any possibilities combing all required formula and show the result in one column? Update regarding Question - 1: I have tried to combine using some other formula to show the required result. But comes up with showing only those zip code which starts with W1 , SW1, EC1, EC2, EC3 & EC4 but can't modify it to restrict those results also where the last character is a number. Here is the code: =IF(ISNUMBER(FIND("W1",(LEFT(K2,2)),1))=TRUE,LEFT(K2,2)&IF(NOT(ISNUMBER(VALUE(RIGHT(K2,1)))), RIGHT(K2,1),""),IF(ISNUMBER(FIND("SW1",K2,1))=TRUE,LEFT(K2,3)&IF(NOT(ISNUMBER(VALUE(RIGHT(K2,1)))), RIGHT(K2,1),""),IF(ISNUMBER(FIND("EC1",K2,1))=TRUE,LEFT(K2,3)&IF(NOT(ISNUMBER(VALUE(RIGHT(K2,1)))), RIGHT(K2,1),""),IF(ISNUMBER(FIND("EC2",K2,1))=TRUE,LEFT(K2,3)&IF(NOT(ISNUMBER(VALUE(RIGHT(K2,1)))), RIGHT(K2,1),""),IF(ISNUMBER(FIND("EC3",K2,1))=TRUE,LEFT(K2,3)&IF(NOT(ISNUMBER(VALUE(RIGHT(K2,1)))), RIGHT(K2,1),""),IF(ISNUMBER(FIND("EC4",K2,1))=TRUE,LEFT(K2,3)&IF(NOT(ISNUMBER(VALUE(RIGHT(K2,1)))), RIGHT(K2,1),""),""))))))

<tbody>
</tbody>

 

[ukmikeb]

Thanks for the feedback.
Does this help :-
texasbear

	D	E	F	G	H	I	J	K	L
2	3RD FLOOR 207 REGENT STREET, LONDON, W1B 3HH							W1B	Ignore
3	42, CHARTERHOUSE SQUARE, LONDON EC1, EC1M 6EU							EC1M	Ignore
4	5, WESTMINSTER GARDENS, LONDON, MARSHAM STREET, SW1P 4JA							SW1P	Ignore
5	160, QUEEN VICTORIA STREET, LONDON, EC4V 4QQ							EC4V	Ignore
6	THE BROADGATE TOWER 20, PRIMROSE STREET, LONDON, EC2A 2RS							EC2A	Ignore
7	BAKERS' HALL 9, HARP LANE, LONDON, EC3R 6DP							EC3R	Ignore
8	BAKERS LTD 9, RAVENSBOURNE AVENUE, LONDON, W11 6DP							W11	Select

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula K2 =TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(D2)," ",REPT(" ",10)),20),10)) L2 =IF(IF(ISNUMBER(SEARCH(" Floor ",D2)),TRUE,IF(ISNUMBER(SEARCH(" House ",D2)),TRUE,IF(ISNUMBER(SEARCH(" Airport ",D2)),TRUE,OR(OR(AND(LEFT(K2,2)="W1",LEN(K2)=2),AND(LEFT(K2,2)="W1",LEN(K2)=3,ISNUMBER(MATCH(RIGHT(K2,1),{"A","B","C","D","E","F","G","H","J","K","P","S","T","U","W"},0)))),AND(OR(LEFT(K2,LEN(K2)-1)={"SW1","EC1","EC2","EC3","EC4"}),LEN(K2)=4,ISNUMBER(MATCH(RIGHT(K2,1),{"A","B","E","H","M","N","P","R","V","W","X","Y"},0))))))),"Ignore","Select") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

It is the combination of both formulae.

hth