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.

Boogie,

You need a few formulas to do that:

In A2, enter using Ctrl-Shift-Enter:
=FIND(CHAR(MIN(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A 1))),1)))),A1,2)

In A3, enter
=LEFT(A1,A2-1)&" " &MID(A1,A2,LEN(A1))

In A4, enter using Ctrl-Shift-Enter
=FIND(CHAR(MIN(CODE(MID(A3,ROW(INDIRECT(A2+1& ":"&LEN(A3))),1)))),A3,2)+1

In A5, enter
=LEFT(A3,A4-1)&" " &MID(A3,A4,LEN(A3))

HTH,
Bernie
MS Excel MVP


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

Try the following user defined function:

Function reformit(r As Range) As String
Dim v As String, done As Boolean
Dim chrct As String
v = r.Value
reformit = Left(v, 1)
done = False
For i = 2 To Len(v)
chrct = Mid(v, i, 1)
If chrct = UCase(chrct) And Not done Then
reformit = reformit & " " & chrct & " "
done = True
Else
reformit = reformit & chrct
End If
Next
End Function

so if A1 contains:
AfjakfhBNjfdlskf

the formula:
=reformit(A1) will display Afjakfh B Njfdlskf


--
Gary''s Student - gsnu200828


"Boogie" wrote:

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.

Boogie wrote:
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.


In 1 cell:

A2 =
LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+1,1) &"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+2,LE N(A1))

***This is an array formula. Commit with CTRL+SHIFT+ENTER ***

Boogie wrote:
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.


In 1 cell:

A2 =
LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+1,1) &"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+2,LE N(A1))

*** This is an array formula. Commit with CTRL+SHIFT+ENTER ***

Glenn wrote:
Boogie wrote:
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.

In 1 cell:

A2 =
LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+1,1) &"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+2,LE N(A1))


***This is an array formula. Commit with CTRL+SHIFT+ENTER ***


Maybe better with forced carriage returns so you don't lose the spaces...

=LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),
MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0) )&" "&
MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),
MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0) +1,1)&" "&
MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),
MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0) +2,LEN(A1))

Glenn wrote:




Sorry for the duplicate post...

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.

Boogie wrote...
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.

Not possible to handle general cases (number of middle names ranging
from 0 to 4) easily using formulas. Also begs the question whether you
have any Scottish or Irish names, e.g., MacNabb, McNeely, O'Brien,
since I doubt you'd want the following transformations.

JohnEMacNab - John E Mac Nabb
SusanMcNeely - Susan Mc Neely
FXO'Brien - F X O' Brien

If you only need to perform this task infrequently, best to use
regular expressions in macros. There are many regular expression
search & replace macros already in the newsgroup archives.

Glenn wrote:
Glenn wrote:
Boogie wrote:
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.

In 1 cell:

A2 =
LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+1,1) &"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+2,LE N(A1))


***This is an array formula. Commit with CTRL+SHIFT+ENTER ***


Maybe better with forced carriage returns so you don't lose the spaces...

=LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),
MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0) )&" "&
MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),
MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0) +1,1)&" "&
MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),
MID(LOWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0) +2,LEN(A1))


And shortened to this with the removal of unnecessary TRANSPOSE and variable
lengths:

=LEFT(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0))&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+1,1)&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+2,LEN(A1))

Still an array formula. And acknowledging Harlan's warnings of strange results
with non-standard names.

Glenn wrote...
....
=LEFT(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0))&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+1,1)&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+2,LEN(A1))
....

If many such formulas are needed, maybe simpler to defined the
following names.

UCS
="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

UCA
={"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L"; "M";
"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z "}

Then use array formulas like

=REPLACE(A1,MIN(FIND(UCA,A1&UCS,2)),1," "&MID(A1,MIN(FIND(UCA,A1&UCS,
2)),1)&" ")

For all of you who contributed to my posting, you all Rock. Thank you!. I
just want let Glenn know that I used his formula since it's a little easier
for me (a newbie) to figure out what he suggested. All the rest, you gave me
something that I have to figure out for a couple of days. :-). Anyways, lots
of thanks.

"Glenn" wrote:

Boogie wrote:
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.


In 1 cell:

A2 =
LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+1,1) &"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+2,LE N(A1))

*** This is an array formula. Commit with CTRL+SHIFT+ENTER ***

Glad I could help. Hopefully, you are using the shortest of my suggestions:

=LEFT(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0))&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+1,1)&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+2,LEN(A1))

Boogie wrote:
For all of you who contributed to my posting, you all Rock. Thank you!. I
just want let Glenn know that I used his formula since it's a little easier
for me (a newbie) to figure out what he suggested. All the rest, you gave me
something that I have to figure out for a couple of days. :-). Anyways, lots
of thanks.

"Glenn" wrote:

Boogie wrote:
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.

In 1 cell:

A2 =
LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+1,1) &"
"&MID(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0)+2,LE N(A1))

*** This is an array formula. Commit with CTRL+SHIFT+ENTER ***