Hello - My worksheet has a range of names (not similar), where some have 
first, mid and last names, some have first and last names, first, last and 
suffix etc. I came across a tutorial that helped me separate first, mid and 
last name, but the forumula doesn't work if there is no middle name.. I want 
to create a macro so I don't have to manually do a text-to-column every time.

Sample data:

Eric M Barnes
Edward L Bassard III
Aurora Sofia Garcia
Angela N Higginbotham
Amanda Marie Roccuzzo
Brent Murakami


Forumla tried - 
Left: =LEFT(A3, SEARCH(" ",A3,1)) --- works good

Mid: =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" 
",A2,1))  ------- works only if there is a middle initial, else returns a 
#VALUE.

Right =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) ---- doesn't work 
if I use the Left and Mid formula, but there is NO middle name/initial.

Please assist ASAP.

Thanks!

For mid try

=IF(ISERROR(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)),"",MID(A2,SEARCH(" 
",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)))

for right try

=IF(ISERROR(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)),RIGHT(A2,LEN(A2)-SEARCH(" 
",A2,1)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)))

-- 
If this is the answer you hoped for please remember to click the yes button 
below...

Kind regards

Rik


"NoviceUser" wrote:

> Hello - My worksheet has a range of names (not similar), where some have 
> first, mid and last names, some have first and last names, first, last and 
> suffix etc. I came across a tutorial that helped me separate first, mid and 
> last name, but the forumula doesn't work if there is no middle name.. I want 
> to create a macro so I don't have to manually do a text-to-column every time.
> 
> Sample data:
> 
> Eric M Barnes
> Edward L Bassard III
> Aurora Sofia Garcia
> Angela N Higginbotham
> Amanda Marie Roccuzzo
> Brent Murakami
> 
> 
> Forumla tried - 
> Left: =LEFT(A3, SEARCH(" ",A3,1)) --- works good
> 
> Mid: =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" 
> ",A2,1))  ------- works only if there is a middle initial, else returns a 
> #VALUE.
> 
> Right =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) ---- doesn't work 
> if I use the Left and Mid formula, but there is NO middle name/initial.
> 
> Please assist ASAP.
> 
> Thanks!
> 
> 

On Thu, 17 Dec 2009 15:52:02 -0800, NoviceUser
<NoviceUser@discussions.microsoft.com> wrote:

>Hello - My worksheet has a range of names (not similar), where some have 
>first, mid and last names, some have first and last names, first, last and 
>suffix etc. I came across a tutorial that helped me separate first, mid and 
>last name, but the forumula doesn't work if there is no middle name.. I want 
>to create a macro so I don't have to manually do a text-to-column every time.
>
>Sample data:
>
>Eric M Barnes
>Edward L Bassard III
>Aurora Sofia Garcia
>Angela N Higginbotham
>Amanda Marie Roccuzzo
>Brent Murakami
>
>
>Forumla tried - 
>Left: =LEFT(A3, SEARCH(" ",A3,1)) --- works good
>
>Mid: =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" 
>",A2,1))  ------- works only if there is a middle initial, else returns a 
>#VALUE.
>
>Right =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) ---- doesn't work 
>if I use the Left and Mid formula, but there is NO middle name/initial.
>
>Please assist ASAP.
>
>Thanks!
>

This macro will parse the cells that you select into the three adjacent
columns.

The First Name is the first space-delimited word in the string.
The Last Name is the last word in the string, UNLESS it is followed by one of
the suffixes that are in the pipe-delimited list of sPat, in which case it
would be the next-to-last word.

The Middle Name is anything in between the first and last names.

As written, the names must consist of letters and/or hyphens.


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 ParseName()
 Dim c As Range, rg As Range
 Dim s As String
 Dim re As Object, mc As Object
 Const sPat As String = _
   "^(\b[A-Z-]+\b)\s*([\sA-Z-.]+)?\s+(\b[A-Z-]" _
   & "+\b)\s*(Jr|Sr|III|II|IV|MD|M\.D\.|PhD\.?)?\s*$"
 
Set re = CreateObject("vbscript.regexp")
    re.Pattern = sPat
    re.ignorecase = True
    
Set rg = Selection 'although could set rg differently
Range(rg.Offset(0, 1), rg.Offset(0, 3)).ClearContents

For Each c In rg
    s = c.Value
    If re.test(s) = True Then
      Set mc = re.Execute(s)
        c.Offset(0, 1).Value = mc(0).submatches(0)
        c.Offset(0, 2).Value = mc(0).submatches(1)
        c.Offset(0, 3).Value = mc(0).submatches(2)
    End If
Next c
End Sub
====================================
--ron