# Editing names

#### nk18

##### Board Regular
Hi,

I have a list of names eg.

Mr John Smith
Mrs Jane June Doe
Dr Stephen Paul William Smth etc.

I would like to change them into two formats:

Mr Smith (title and surname)
&
Mr J J Smith (title, intial(s) and surname)

As there is no set length for the names I have struggled using the replace and substitute functions but to no avail. Is it possible to solve this with a formula? Any help would be very much appreciated.

Thanks,

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi

really not sure if this is correct or nor
Code:
``````Sub SplitName()
Dim i As Long, ii As Integer, LastR As Long, r As Range
Dim x() As String, ttl As String, Intl As String, z() As String
x = Split("Mrs ,Mrs.,Mr,Mr.,Miss,Ms,Ms.,Dr,Dr.", ",")
Application.ScreenUpdating = False
With ActiveSheet
LastR = .Range("a65536").End(xlUp).Row
For Each r In .Range("a1", .Range("a65536").End(xlUp))
For i = LBound(x) To UBound(x)
If InStr(UCase(r), UCase(x(i))) > 0 Then
ttl = Trim(x(i))
Exit For
End If
Next
ii = 0
For i = 1 To Len(r)
If Mid(r, i, 1) = " " Then
ii = ii + 1
ReDim Preserve z(1 To ii)
z(ii) = i
End If
Next
For i = 1 To UBound(z) - 1
Intl = Intl & " " & Mid(r, z(i) + 1, 1)
Next
r.Offset(, 1) = ttl & Mid(r, InStrRev(r, " "), 9 * 9)
r.Offset(, 2) = ttl & Intl & Mid(r, InStrRev(r, " "), 9 * 9)
ttl = ""
Intl = ""
Erase z
Next
End With
Application.ScreenUpdating = True
End Sub``````
hope this helps
jindon

Thanks jindon.

Replies
6
Views
618
Replies
11
Views
829
Replies
11
Views
602
Replies
5
Views
1K
Replies
16
Views
360

1,206,755
Messages
6,074,756
Members
446,083
Latest member
kfunt

### 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.

### Which adblocker are you using?

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