Hi

On the assumption that there may be cases with no Middle Initial, several
middle initials and spaces already within the name, then the following code
should work for you.
I would work on a COPY of your data, in case it doesn't do what you want.


I used column K for my data.
Change the column letter to suit your use.

Sub SplitName()
Dim i As Long, j As Long, lr As Long
Dim oldname As String, newname As String
Dim nchar1 As String, nchar2 As String
lr = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To lr

oldname = Cells(i, "K").Value
If oldname < "" Then
newname = Left(oldname, 1)
For j = 2 To Len(oldname)
nchar1 = Mid(oldname, j - 1, 1)
nchar2 = Mid(oldname, j, 1)
If nchar1 = " " Then
newname = newname + nchar2
ElseIf nchar2 = " " Then
newname = newname & nchar2
ElseIf UCase(nchar2) = nchar2 And _
UCase(nchar1) = nchar1 Then
newname = newname & " " & nchar2
ElseIf UCase(nchar2) = nchar2 And _
UCase(nchar1) < nchar1 Then
newname = newname & " " & nchar2
Else
newname = newname & nchar2
End If
Next j

Cells(i, "K") = newname
End If
Next i

End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up Macros
Highlight the macro name
Run

--
Regards
Roger Govier

"Boogie" wrote in message
...
Example: In A1 BoogieMByron. How can make it as in A2 Boogie M Byron.
There's
hundreds of names whereby Firstname,MI,LastName are all in one word. I
want
to cleate a space before and after Middle initial. Thanks for your help.