Converting full Names from 1 cell to 2

[samcannet1]

I deal with an Excel file that has many names on it all in 1 cell.
Some of the common combinations are below. Is there any way to
separate the 1 column in to 2 columns bases on table below.

FIRST LAST CONVERT TO FIRST LAST

FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST

FIRST M LAST CONVERT TO FIRST M LAST

FIRST M. LAST CONVERT TO FIRST M. LAST

FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX

FIRST MIDDLE LAST SUFFIX
CONVERT TO FIRST MIDDLE LAST SUFFIX


FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX

The problem I have is that all the names are different lengths.

Thanks for your help.

 

[Guest]

The first thing I might so is hilight the cells and go 'Data'....'text to
columns'....choose delimited then click in the 'space' box (unclick tab etc)

this should them divide the cells by the space between the names giving you
a 1st name or middle name or last name in cells. You'd then have to
manipulate them , perhaps by sorting to move columns around so that all the
middle names are in the same column and all the last names in the same column.

 

[Tim879]

the easiest way to do this is to go to Data->Text to Columns and
follow the wizard. this will at least get you all the names in
separate columns

 

[Tim879]

this is a slightly different approach... but one thing to consider.
Write a macro to delete all of the suffixes (i.e. find and replace Jr,
Sr, etc.).

Next use the left and right functions to get the first and last names
of the user.

to get the first name:
=LEFT(A1,FIND(" ",A1))

to get the last name (assumes last name occurs after the last space in
the cell)
=MID(A1,FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN(" "&A1)-
LEN(SUBSTITUTE(" "&A1," ",""))))-1,1000)

you might also find this site helpful.
http://www.dailydoseofexcel.com/archives/category/worksheet-functions/string-functions/

 

[Ron Rosenfeld]

I deal with an Excel file that has many names on it all in 1 cell.
Some of the common combinations are below. Is there any way to
separate the 1 column in to 2 columns bases on table below.

FIRST LAST CONVERT TO FIRST LAST

FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST

FIRST M LAST CONVERT TO FIRST M LAST

FIRST M. LAST CONVERT TO FIRST M. LAST

FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX

FIRST MIDDLE LAST SUFFIX
CONVERT TO FIRST MIDDLE LAST SUFFIX


FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX

The problem I have is that all the names are different lengths.

Thanks for your help.

The hard part is determining the Suffix.

Here's one approach with a UDF.

Suffix is a NAME'd cell containing a pipe delimited list of valid Suffixes.
For example: Jr|Sr|II|III|IV|MD|PHd

<alt-F11> opens the VB Editor. Ensure your project is highlighted in the
project explorer window, then Insert/Module and paste the code below into the
window that opens.

To use this:

A1: Full Name
B1: =TRIM(SUBSTITUTE(TRIM(A1),C1,""))
C1: =ReExtr(TRIM(A1),"\w+(\s("&Suffix&"))?$")

=========================================================
Option Explicit
Function ReExtr(str As String, sPattern As String) As String
'Requires setting reference to Microsoft VBScript Regular Expressions 5.5

Dim re As RegExp
Dim mc As MatchCollection

Set re = New RegExp
With re
.Global = True
.IgnoreCase = True
.Pattern = sPattern
End With

If re.Test(str) = True Then
Set mc = re.Execute(str)
ReExtr = mc(0)
End If
End Function
=====================================
--ron

 

[Tim879]

I was able to get this solution to work:

Note: Row 1 is column headings so my data (and the related formulas)
start on Row 2

Col. A holds the person's name:
Tom Jones
Tom Jones Jr
Tom Jones Sr.
Tom Mike Jones Sr.
Tom M. Jones Sr.

Col. B is a formula to get the first name (returns all the characters
up to the first space)
=LEFT(A2,FIND(" ",A2))

Col. C is a helper column to determine if there is a middle name:
=IF(ISERROR(MID(A2,LEN(B2)+1,FIND("
",MID(A2,LEN(B2)+1,1024)))),"",MID(A2,LEN(B2)+1,FIND("
",MID(A2,LEN(B2)+1,1024))))

This function will return the second name (i.e. characters from the
second space to the third space in the string). If there is no 3rd
space, then the person has no middle name and the formula returns ""

Col. D determines whether Col. C actually returned the middle name or
the last name by comparing the value in Col. C to the value in Col. E
(the last name - see below)
=IF(EXACT(C2,TRIM(F2)),"",C2)

Col. E returns the last name less any suffixes. The suffix is
determined in Col. F (see below). This formula returns the last name
in the string less the suffix (if any)
=MID(TRIM(LEFT(A2,LEN(A2)-LEN(F2))),FIND("^^",SUBSTITUTE("
"&TRIM(LEFT(A2,LEN(A2)-LEN(F2)))," ","^^",LEN(" "&TRIM(LEFT(A2,LEN(A2)-
LEN(F2))))-LEN(SUBSTITUTE(" "&TRIM(LEFT(A2,LEN(A2)-LEN(F2))),"
",""))))-1+1,1024)

Col. F - J determine the suffix. Ron's UDF in the previous post may
be a better solution than mine but here's how I did it.
Cells G1:J1 include the suffix I want to look for (e.g. G1 = Jr, H1 =
Sr, I1 = Jr., J1 = Sr.
I put the following formula in cells G2:J2
=IF(ISERROR(FIND(G$1,RIGHT($A2,LEN(G$1)))),"",G$1)

this looks for the suffix as the last n characters of the string, with
n being the length of the suffix typed in G1:J1. If it matches, then
it returns the suffix. If not, it returns blank.

Finally, Col. F returns the concatenation of the values in G2:J2
=G2&H2&I2&J2

For example, if the suffix was Jr. then using the values above, G2 =
"", H2="", I2="Jr.", J2="" and F2 would = ""&""&"Jr."&"" or "Jr."

Finally, in col's K and L, I put the names together as requested in
the original post:
Col K: First / Middle name =B2&D2
Col L: Last / Suffix =TRIM(E2&" "&F2)

If anyone wants to see the excel file, just email me.

Tim