complicated text to column

[dfw]

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?

 

[מיכ×ל (מיקי) ×בידן]

As your data is not Consistence - there might be a way around with formulas
that brake the names into 3 parts but I would suggest that you start by
examining "Text to Columns" fro the DATA menu
Micky

 

[JLatham]

Based initially on Chip Pearson's solution for names in the "last, First M."
format, I came up with these.

Assume name in A2,
in B2, use this formula:
=LEFT(A2,IF(ISERROR(FIND(" ",A2,1)),LEN(A2),FIND(" ",A2,1)-1))

In C2 use this formula (remember, all one line)
=IF(ISERR(FIND(" ",A2,FIND(" ",A2)+1)),"",TRIM(IF(ISERROR(FIND("
",A2,1)),A2,MID(A2,FIND(" ",A2,1)+1,IF(ISERROR(FIND(" ",A2,FIND("
",A2,1)+2)),LEN(A2),FIND(" ",A2,FIND(" ",A2,1)+2))-FIND(" ",A2,1)))))

In D2 use this formula, again remember, all 1 line:
=IF(C2<>"",TRIM(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" ",A2,FIND(" ",A2,FIND("
",A2,1)+2))),LEN(A2),FIND(" ",A2,FIND(" ",A2,FIND("
",A2,1)+2))-1))),TRIM(IF(ISERROR(FIND(" ",A2,1)),A2,MID(A2,FIND("
",A2,1)+1,IF(ISERROR(FIND(" ",A2,FIND(" ",A2,1)+2)),LEN(A2),FIND("
",A2,FIND(" ",A2,1)+2))-FIND(" ",A2,1)))))

 

[Ron Rosenfeld]

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

 

[B. R.Ramachandran]

Hi,

I came up with the following approach, which will work even if there is more
than one middle initial, middle name, or any combination thereof.

If the name is in A2, enter the following formulas in B2, C2, and D2.

in B2,
=LEFT(A2,FIND(" ",A2)-1)

In C2,
=IF(LEN(TRIM(A2))=LEN(B2)+LEN(D2)+1,"
",MID(TRIM(A2),LEN(B2)+2,LEN(TRIM(A2))-LEN(B2)-LEN(D2)-2))

In D2,
=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND("#",SUBSTITUTE(TRIM(A2),"
","#",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ","")))))


Note: The formula in C2 will work only after the formula in D2 is also
entered.

One issue with my formulas:
If the last name ends with a suffix such as "Sr", "Jr" or a roman numeral
such as "II" or "III" (with a space between the name and the suffix), the
formulas would misinterpret the middle and last names and wrongly return only
the suffix as the last name (i.e., anything after the last space in the
entire name).

Secondly, there should be a space between an intital and a name, a name and
an initial, or two initials. Thus "William J. Burrows" will work but not
"William J.Burrows"

Please click "Yes" if this is helpful.

Best regards,
B. R. Ramachandran