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))