On Wed, 9 Dec 2009 12:01:03 -0800, dfw wrote:

My imported "Name" column (column A) contains different name formats: First
Name Last Name; First Name Middle Initial Last Name; and, in some cases,
First Name Middle Name Last Name (for example: Mary Smith, John B. Doe,
William James Burrows).
Is there a simple way to convert all names to three columns with the Given
Name in Column B, the Middle Initial or Name in Column C, and the last name
in Column D?

If, in fact, you always have either two or three space separated words to
parse, then it is relatively simple to do this with a VBA Macro.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range to parse. Then <alt-F8 opens
the macro dialog box. Select the macro by name, and <RUN.

===================================
Option Explicit
Sub ParseNames()
'Acceptable formats:
' FN MN LN
' FN MI LN
' FN LN
Dim c As Range
Dim rg As Range
Dim sFullName As Variant

Set rg = Selection 'various ways to set this
For Each c In rg
sFullName = Split(c.Value, " ")
c.Offset(0, 1).Value = sFullName(0)
c.Offset(0, 3).Value = sFullName(UBound(sFullName))
If UBound(sFullName) = 2 Then
c.Offset(0, 2).Value = sFullName(1)
End If
Next c
End Sub
====================================

If you need to do this with formulas, then

B1: =LEFT(A1,FIND(" ",A1)-1)

C1:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",
TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99)))

D1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

-------------------------------

However, if your names do not fall precisely into those formats, then these
methods will not work reliably. It may be possible, or not, depending on the
real variety in your name formats.
--ron