I have a bunch of addresses where the last word of each address needs to be
split. Eg. edgeware road london. I would like edgeware road to stay in its
cell and london to be removed and placed in the cell to the right.

A formula or vba solution would be appreciated.
--
Thanks in advance,
MarkN

Mark how do yuo cope with 'North Shields' or 'Milton Keynes'?

Here is a VBA solution as a function.

Put a 1 in as the town and 0 or blank for the road:

Function split_address(s As String, Optional item As Integer = 0) As String
Dim pos As Integer
Dim s_town As String, s_road As String

s = Trim(s) ' remove spaces leading and trailing spaces
pos = InStr(1, StrReverse(s), " ")

s_town = Right(s, pos - 1)
s_road = Left(s, Len(s) - pos + 1)
split_address = IIf(item = 1, s_town, s_road)
End Function


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"MarkN" wrote:

I have a bunch of addresses where the last word of each address needs to be
split. Eg. edgeware road london. I would like edgeware road to stay in its
cell and london to be removed and placed in the cell to the right.

A formula or vba solution would be appreciated.
--
Thanks in advance,
MarkN