Parse words out of string

[Nuz]

I have a string consisting of multiple words which I'd like to break down into separate words by VBA. For example the initial string is an address like "Street 66 123456 LONDON". I need to parse the city, postal code and the street out of that string and put them into separate variables.

The problem is that sometimes the string can be like "Street 66 123456 LONDON (CITY), i.e. the city is not one word but two or more. That's why for the postal code I can't always just find the second word from the right since it is not always the postal code.

I know that:
-the city is always the right-most word or words,
-postal code is always left of city
-street address is everything else and starts always from the left

Is there a foolproof way to parse out these words from the initial string?

 

[Peter_SSs]

I would put the process this way:

1) For postal code, give the first series of numbers (any number of digits) iterating from the right
2) For city, give everything that lies on the right side of the postal code
3) For address, give everything that lies on the left side of the postal code

For example, "Street 66 99 66 2345 LONDON" would be broken down to "Street 66 99 66" , "2345", "LONDON"

I was also thinking of a RegExp solution. Mine is a standard macro rather than a user-defined function. It assumes data starts in cell A2.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ParseAddress()<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">With</SPAN> CreateObject("VBScript.RegExp")<br>        .Pattern = "( \d+ )(?=\D+$)"<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp))<br>            s = c.Value<br>            <SPAN style="color:#00007F">If</SPAN> .test(s) <SPAN style="color:#00007F">Then</SPAN><br>                c.Offset(, 1).Resize(, 3).Value = Split(.Replace(s, "|$1|"), "|")<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Sample data and results:

Excel Workbook
	A	B	C	D
1
2	Street 66 123456 LONDON	Street 66	123456	LONDON
3	XYZ
4	Street 66 123456 LONDON (CITY)	Street 66	123456	LONDON (CITY)
5	62 North Street 66 00123456 LONDON (CITY)	62 North Street 66	00123456	LONDON (CITY)
6	Street 66 012345 LONDON	Street 66	012345	LONDON
7
8	42 8864 56 6549 New York City	42 8864 56	6549	New York City
9	Street 66-99 012345 LONDON	Street 66-99	012345	LONDON
10	Street 66 (Street 88) 123 LONDON	Street 66 (Street 88)	123	LONDON
11	Street 66 / Street 88 012345 LONDON	Street 66 / Street 88	012345	LONDON
12	Street 66 012345 LONDON (CITY)	Street 66	012345	LONDON (CITY)
Parse words

 

[Nuz]

Both suggestions work perfectly.
That was something new for me. I haven't heard of RedExp before.

Very nice work!

 

[Peter_SSs]

I haven't heard of RedExp before.

That was a typo from me (fixed now). It should be RegExp (Regular Expression). An introduction here if you are interested.