Split last word into new cell
 

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

Split last word into new cell
 

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

Split last word into new cell
 

Mark

There is an small error in the code.

s_road = Left(s, Len(s) - pos +1)

should be

s_road = Left(s, Len(s) - pos)

see <<<<<< below.

Now it add a space on the end.
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 ) '<<<<<
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:

Thanks Martin, you're a champion. I have about four offending double-barrel
town names only so I have just found "Milton Keynes", replace with
"MiltonKeynes".
--
Thanks again,
MarkN


"Martin Fishlock" wrote:

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

Split last word into new cell
 

I have actually got something else to work...

Sub SepLastTerm()
'This macro will remove the last word from a text string and place it into
the cell to the right.
'Select the range (an entire column is okay) before activating the macro.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim iRows As Long, mRow As Long, ir As Long
iRows = Selection.Rows.Count
Set lastcell = Cells.SpecialCells(xlLastCell)
mRow = lastcell.Row
If mRow < iRows Then iRows = mRow
For ir = 1 To iRows
If Len(Trim(Selection.Item(ir, 1).Offset(0, 1))) < 0 Then
iAnswer = MsgBox("Found non-blank in adjacent column -- " _
& Selection.Item(ir, 1).Offset(0, 1) & " -- in " & _
Selection.Item(ir, 1).Offset(0, 1).AddressLocal(0, 0) & _
Chr(10) & "Press OK to process those that can be split", _
vbOKCancel)
If iAnswer = vbOK Then GoTo DoAnyWay
GoTo terminated
End If
Next ir
DoAnyWay:
For ir = 1 To iRows
If Len(Trim(Selection.Item(ir, 1).Offset(0, 1))) < 0 _
Then GoTo nextrow
checkx = Trim(Selection.Item(ir, 1))
L = Len(Trim(Selection.Item(ir, 1)))
If L < 3 Then GoTo nextrow
For im = L - 1 To 2 Step -1
If Mid(checkx, im, 1) = " " Then
Selection.Item(ir, 1) = Left(checkx, im - 1)
Selection.Item(ir, 1).Offset(0, 1) = Trim(Mid(checkx, im + 1))
GoTo nextrow
End If
Next im
nextrow:
Next ir
terminated:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
--
Thanks,
MarkN


"Martin Fishlock" wrote:

Mark

There is an small error in the code.

s_road = Left(s, Len(s) - pos +1)

should be

s_road = Left(s, Len(s) - pos)

see <<<<<< below.

Now it add a space on the end.
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 ) '<<<<<
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:

Thanks Martin, you're a champion. I have about four offending double-barrel
town names only so I have just found "Milton Keynes", replace with
"MiltonKeynes".
--
Thanks again,
MarkN


"Martin Fishlock" wrote:

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