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>

 

[mole999]

Ec2a and ec2 are technically the same area

 

[shekhor]

Ec2a and ec2 are technically the same area

Thank you for your response but your answer is no where near to my question. Neither EC2 nor EC2a is the only concern. Please read the whole thread again and if you are able to help than help me.

 

[mole999]

two things

UK uses POSTCODES not the American ZIP Code

and if you search the forums these older threads do appear

http://www.mrexcel.com/forum/excel-questions/655611-identify-extract-postcode-address.html

 

[ukmikeb]

Hi

I believe this formula will show you where the Post Codes of W1 , SW1, EC1, EC2, EC3 & EC4 are valid, so that you can ignore them :-

=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))))

referencing column K where your formula :-

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(D2)," ",REPT(" ",60)),120),60))

is placed and btw can be reduced to :-

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(D2)," ",REPT(" ",10)),20),10))

.

And using the top formula will certainly reduce any possibility of the need for five helper columns to sort the wheat from the chaff.

hth

 

[gaz_chops]

Create a small table with the valid codes, like this sorted A-Z

[TABLE="width: 65"]
<tbody>[TR]
[TD="width: 65"]EC1[/TD]
[/TR]
[TR]
[TD]EC2[/TD]
[/TR]
[TR]
[TD]EC3[/TD]
[/TR]
[TR]
[TD]EC4[/TD]
[/TR]
[TR]
[TD]SW1[/TD]
[/TR]
[TR]
[TD]W1[/TD]
[/TR]
</tbody>[/TABLE]

Then try

=IFERROR(LOOKUP(1,1/($J$1:$J$6=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(D1)," ",REPT(" ",60)),120),60)))),"")

where $J$1:$J$6 is the new table you created, this will return 1 for EC1, EC2 etc and Blank for EC1M etc

 

[shekhor]

two things

UK uses POSTCODES not the American ZIP Code

and if you search the forums these older threads do appear

http://www.mrexcel.com/forum/excel-questions/655611-identify-extract-postcode-address.html

Still that post don't have the solution which I am looking for. But thank you for your help

 

[shekhor]

Hi

I believe this formula will show you where the Post Codes of W1 , SW1, EC1, EC2, EC3 & EC4 are valid, so that you can ignore them :-

=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))))

referencing column K where your formula :-

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(D2)," ",REPT(" ",60)),120),60))

is placed and btw can be reduced to :-

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(D2)," ",REPT(" ",10)),20),10))

.

And using the top formula will certainly reduce any possibility of the need for five helper columns to sort the wheat from the chaff.

hth

Hello @ukmikeb, Thank you for your help. It worked like charm for my query related to the postal codes of UK. Now I want to request you if you can help me further in this case which if there is any way to show the first segment of the zip code instead of "True" as output in the new column by modifying your given formula.

Another thing is there any way to integrate the code for searching floor, house and airport along with the first query? It will help me to keep noticing only in one column only. Thank you very very much for the help. You just made my day.

 

[shekhor]

Create a small table with the valid codes, like this sorted A-Z

[TABLE="width: 65"]
<tbody>[TR]
[TD="width: 65"]EC1[/TD]
[/TR]
[TR]
[TD]EC2[/TD]
[/TR]
[TR]
[TD]EC3[/TD]
[/TR]
[TR]
[TD]EC4[/TD]
[/TR]
[TR]
[TD]SW1[/TD]
[/TR]
[TR]
[TD]W1[/TD]
[/TR]
</tbody>[/TABLE]

Then try

=IFERROR(LOOKUP(1,1/($J$1:$J$6=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(D1)," ",REPT(" ",60)),120),60)))),"")

where $J$1:$J$6 is the new table you created, this will return 1 for EC1, EC2 etc and Blank for EC1M etc

Sorry this code didn't work. But still want to thank you for your afford.

 

[gaz_chops]

In what way did it not work?

[TABLE="width: 561"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table[/TD]
[/TR]
[TR]
[TD]3RD FLOOR 207 REGENT STREET, LONDON, W1B 3HH[/TD]
[TD][/TD]
[TD][/TD]
[TD]EC1[/TD]
[/TR]
[TR]
[TD]42, CHARTERHOUSE SQUARE, LONDON EC1, EC1M 6EU[/TD]
[TD][/TD]
[TD][/TD]
[TD]EC2[/TD]
[/TR]
[TR]
[TD]5th Floor, WESTMINSTER GARDENS, LONDON, MARSHAM STREET, SW1P 4JA[/TD]
[TD][/TD]
[TD][/TD]
[TD]EC3[/TD]
[/TR]
[TR]
[TD]160, QUEEN VICTORIA STREET, LONDON, EC4V 4QQ[/TD]
[TD][/TD]
[TD][/TD]
[TD]EC4[/TD]
[/TR]
[TR]
[TD]THE BROADGATE TOWER 20, PRIMROSE STREET, LONDON, EC2A 2RS[/TD]
[TD][/TD]
[TD][/TD]
[TD]SW1[/TD]
[/TR]
[TR]
[TD]BAKERS' HALL 9, HARP LANE, LONDON, EC3R 6DP[/TD]
[TD][/TD]
[TD][/TD]
[TD]W1[/TD]
[/TR]
[TR]
[TD]5, WEST House MINSTER GARDENS, LONDON, MARSHAM STREET, SW1 4JA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]160, QUEEN VICTORIA STREET, LONDON, EC4 4QQ[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]THE Airport BROADGATE TOWER 20, PRIMROSE STREET, LONDON, EC2 2RS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BAKERS' HALL 9, HARP LANE, LONDON, EC3 6DP[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]