Extracting right-hand word from text

No Waiting

New Member
I have a simply problem, but am having trouble finding the solution!

I have a column like this -

Mr & Mrs John Brown
Jane Smith
Mr Tom Gray
Peter Hancock

and need to split it into two columns, thus

Mr & Mrs John
Jane
Mr Tom
Peter
Miss Jill

and

Brown
Smith
Gray
Hancock

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jindon

MrExcel MVP
Hi,

the code will split ;
Title in Col.C
First Name in Co.D and
Last Name in Col.E

Code:
``````Sub split_name()
Dim i As Integer, a(7) As String, b() As String
Dim x As Integer, y As String, r As Range
a(0) = "MR."
a(1) = "MR "
a(2) = "MRS."
a(3) = "MRS "
a(4) = "MISS "
a(5) = "MS."
a(6) = "MS "
a(7) = "& "
Application.ScreenUpdating = False
With ActiveSheet
.Columns("c:e").Clear
For Each r In .Range("a1", .Range("a65536").End(xlUp))
For i = LBound(a) To UBound(a)
If InStr(UCase(r), a(i)) > 0 Then
x = x + 1
End If
Next
b = Split(r)
If x = 0 Then
For i = LBound(b) To UBound(b)
.Cells(r.Row, i + 4) = b(i)
Next
Else
For i = 0 To x - 1
y = y & b(i) & " "
Next
.Cells(r.Row, 3) = Left(y, Len(y) - 1)
ii = 5
For i = x To UBound(b)
.Cells(r.Row, i + 4 - x) = b(i)
Next
End If
x = 0
y = ""
Erase b
Next
End With
Erase a
Application.ScreenUpdating = True
End Sub``````
if you want to add more items to the title array like DR PROF etc
simply increase number of a(7) in the Dim statement like a(11) and add
a(8)="DR."
a(9)="DR "
a(10)="PROF."
a(11)="PROF "

rgds,
jindon

No Waiting

New Member
Many thanks guys - you've saved the day

Have a drink on me !

Replies
6
Views
565
Replies
8
Views
679
Replies
1
Views
784
Replies
4
Views
1K
Replies
2
Views
336

1,182,032
Messages
5,933,354
Members
436,889
Latest member
vamsi nandan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back