Fullname split

Will all six string segments always be present?

Results:

The previous attempt ignored the suffix.

This one attempts to deal with it but if an entry comprises firstname, lastname  & suffix, it will be treated as firstname, middlename & lastname. The only way round that is to have some way of distinguishing between a last name and suffix.

Sub splitNamestring2()
    Dim i As Integer
    Dim n As Integer
    Dim Names() As String
    Dim TempNames() As String
    Dim FullNames() As String
    Dim strNameString As String
    
    strNameString = "MICHELLE J lastname & JOE E lastname"
    FullNames = Split(strNameString, " & ")
    ReDim Names(3, UBound(FullNames))
    
    For i = 0 To UBound(FullNames)
        TempNames = Split(FullNames(i), " ")
        Names(0, i) = TempNames(0) 'first name
        n = UBound(TempNames)
        'first name
        Select Case n
            'last name
            Case 1
                Names(2, i) = TempNames(1) 'last name
            Case 2
                Names(1, i) = TempNames(1) 'middle name
                Names(2, i) = TempNames(2) 'last name
            Case 3
                Names(1, i) = TempNames(1) 'middle name
                Names(2, i) = TempNames(2) 'last name
                Names(3, i) = TempNames(3) 'suffix
        End Select
    Next i
End Sub

Select allOpen in new window

There is a space between the lastname and the suffix.

Thak you.

qeury16 gave me this error.

Error in attached file
test-for-fixed.docx

When you open the database, press Alt+F11 to go immediately into the into the VBA Editor.
Then click: Debug-->Compile
Then run the compact/Repair utility
Then open the query

I aplogize I did not import the module1. That correct the error.
i is not splitting this name correct
DAVID L & KATHRYN J lastname

DAVID,L,&,KATHRYN,J,lastname

fn1=DAVID
mn1=L
ln1=&
fn2=J
mn2=lastname
ln2=blank or space
yourname2=DAVID,L,&,KATHRYN,J,lastname

Thank you

Are any of your comments relevant to my contribution? If not, I'll stop now.

I'm using Boag2000 query examples. Thank you for your help.

<i is not splitting this name correct
DAVID L & KATHRYN J lastname>
...That is not the original format you specified...

What you specified was:
MICHELLE J lastname & JOE E lastname
(all six names are present)
...besides, you are not telling us what you want returned in each field, for each different "name" format/combination.

This is why I asked in:
https://www.experts-exchange.com/questions/28236751/Fullname-split.html?anchorAnswerId=39484184#a39484184
   "Will all six string segments always be present? "
...to which you never replied...?

There is no one function that will catch every possible name combination. (missing middle names(s), ...etc, one missing last name, two middle names, ...etc)
 So all I can offer is what I posted...

JeffCoachman

GrahamSkan may be willing to customize his solution for this new contingency.

Again, all I can offer is what I posted as per your original requirement...

...but as I stated, if there are more than two possible Name "combinations", then this may become more complicated than it is worth.

JeffCoachman

Sir, when i posyed the question I list the following.

There will be other data in the fullname field:
firstname lastname
firstname middlename last name
 and therw can suffix
firstname middlename lastname suffix

Each name is seperate by a space.

When there two firsr name ther always be &.  That is way I can tell that ther two first names

OK. This is a collaborative forum. What we post here can be searched for and seen by others. Contributions hidden in links will not appear in any search.

It doesn't apply here, but off-site links have a tendency to evaporate and become useless in the future.

Also, this is intended to be a 'how-to', as opposed to a 'do-it-for-you' forum, so it is better to see what you, as well as later searchers, can learn without following what could turn out to be a series of links.

GrahamSkan,

Was you post above meant for this thread?

Jeff

Sorry sir I posted the points on wrong name. How do I change my error

click the request Attention link and explain what it is that you want to do...

Jeez,  I am confident that I gave good advice, but I thought that you were ignoring me

I'm a bit confused too...