View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Converting full Names from 1 cell to 2

On Tue, 14 Aug 2007 12:09:09 -0700, wrote:

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