Make address field in separate columns

[tapzo]

Hello all,

Hoping somebody may be able to help with this as I've tried many different solution with no luck!

My issue is that I get addresses exported into a spreadsheet with all the address information put into one column.
These addresses have no commas, and follow different structures to each other.

Is it at all possible to have these somehow put into separate fields?

Address Line 1
Address Line 2
Address Line 3
City
Postcode

I've tried different options with some success but never perfect due to each address being different in length (some addresses are just 1 address line, city and postcode, where some are multiple address lines, then city then postcode).

Example of 1 Address line with city and postcode, sections separated by brackets:
(123 Example Road) (London) (W1 111)

Example of multiple Address line with city and postcode, sections separated by brackets
(Unit 5) (123 Example Road) (Grange Hill) (London) (W1 111)

Hope this makes sense.

Companies-House-search-results.csv
	B
2	20-22 Wenlock Road London N1 7GU
3	c/0 Aacsl Accountants Ltd 1st Floor North Westgate House Harlow CM20 1YS
4	71-75 Shelton Street Covent Garden London WC2H 9JQ
5	71-75 Shelton Street Covent Garden London WC2H 9JQ
6	Flat 2 45 Cottage Grove Southsea PO5 1EH
7	20-22 Wenlock Road London N1 7GU
8	2 St Matthews Court 1 Meadow Row London SE1 6RG
9	Waylea Mawgan Porth Newquay TR8 4DA
10	71-75 Shelton Street Covent Garden London WC2H 9JQ
11	71-75 Shelton Street Covent Garden London WC2H 9JQ
12	Flat G02 Trajan Court 56 Usher Road London E3 2HB
13	20 Silver Birch Avenue North Weald Epping CM16 6LB
14	Flat 96 Queens Court Queensway London W2 4QR
15	124 Jack Clow Road London E15 3AS
16	27 Old Gloucester Street London WC1N 3AX
17	16 Great Queen Street London WC2B 5DG
18	58 Ford Road Dagenham RM10 9JR
19	5 Hermes Crescent Coventry CV2 1HY
20	159 Velveteen Crescent Manchester M28 3ZR
21	71-75 Shelton Street Covent Garden London WC2H 9JQ
22	27 Old Gloucester Street London WC1N 3AX
23	Flat 3, 49 Kay Street London E2 8QB
24	71-75 Shelton Street Covent Garden London WC2H 9JQ
25	32 Basegreen Drive Sheffield S12 3FF
26	291 Brighton Road South Croydon CR2 6EQ
27	117 Norland House 9 Queensdale Crescent London W11 4TN
28	71-75 Shelton Street Covent Garden London WC2H 9JQ
29	9 Perseverance Works Kingsland Road London E2 8DD
30	6 Malvern Close Woodley Reading RG5 4HW
31	17 Whiterock Drive Belfast BT12 7PR
32	369b Uxbridge Road Pinner HA5 4JN
33	71-75 Shelton Street Covent Garden London WC2H 9JQ
34	Bank House 8 Cherry Street Birmingham B2 5AL
35	71-75 Shelton Street Covent Garden London WC2H 9JQ
Companies-House-search-results

 

[OaklandJim]

This does not seem possible given that there is no good way to tell where street ends and city code begins. A challenging example is this address 6 Malvern Close Woodley Reading RG5 4HW. In that example there is no street designator to look for (Street, Drive, Road). In this example 71-75 Shelton Street Covent Garden London WC2H 9JQ it is not clear what Covent Garden is. Is that a building name? Name of the location? In this example 6 Malvern Close Woodley Reading RG5 4HW it would not be possible to tell where the street name ends and city begins. Here is an especially challenging example: Waylea Mawgan Porth Newquay TR8 4DA. Cannot tell where street, location and city start and end.

 

[6StringJazzer]

You are asking for a simple way to do something in Excel that people pay many thousands of dollars to do with products specifically built to do this, which also have access to postal data to verify addresses.

some success but never perfect

That is the best you are ever going to do with this problem using formulas in Excel. It might be possible to do something more reliable using VBA. But this is not structured data. For example, are there cities that have more than one word?

This could be a lot easier if you could build a list of all postal codes and their corresponding cities. Does the Royal Mail (or whatever you've got there) publish anything like that?

 

[rhombus4]

Post code list from here

List of postcode areas in the United Kingdom - Wikipedia

en.m.wikipedia.org

And select

United Kingdom postcode areas​

 

[Kerryx]

You can use this to extract postcodes, once you have postcode you should be able to use the wiki data to get cities or some other source to link postcodes to address, like previous posters have said due to lack of structure it would nigh be impossible to extract the data cleanly as you requested within my capabilities anyway.

Book1
	A	B
1	Address	Postcode
2	20-22 Wenlock Road London N1 7GU	N1 7GU
3	c/0 Aacsl Accountants Ltd 1st Floor North Westgate House Harlow CM20 1YS	CM20 1YS
4	71-75 Shelton Street Covent Garden London WC2H 9JQ	WC2H 9JQ
5	71-75 Shelton Street Covent Garden London WC2H 9JQ	WC2H 9JQ
6	Flat 2 45 Cottage Grove Southsea PO5 1EH	PO5 1EH
7	20-22 Wenlock Road London N1 7GU	N1 7GU
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=RIGHT(SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1),LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))

 

[Fluff]

You wont even be able to get the towns/cities as CM20 is Chelmsford, but your address is Harlow & PO5 is Portsmouth & you have Southsea.

 

[Kerryx]

Well that sorts that then

 

[Joe4]

Yes, this is the classis question we see when trying to parse address or names. Without any sort of delimiter (other than spaces), since there really are no rules to determine how many words might be in a street address, city name, first name, or last name, it is virtually impossible to come up with a formula that will work in all cases.
Usually the best you can do is come up with a formula that works in a majority of the cases, but will still require you to review and manually fix all the others.

I usually tell people, if you cannot explain the methodology/rules in plain English, how can you expect to program something to do it?
Excel isn't magic, it cannot do what you cannot describe. It is very literal and can only do what we explicitly tell it to do.

 

[6StringJazzer]

Here is an example of one of the many commercial services that do this. These services exist because you just can't do it in Excel.

I have not used this service and I am not recommending it. I am just offering it to show that such services exist.

Address Cleansing | What It Is and How to Do It

Address Cleansing | Have some addresses that you need to clean? We'll explain the process and how to do it.

www.smarty.com