Fullname split

Please share the vba code to split this full name.

MICHELLE J lastname & JOE E lastname

Out put
last name= lastname

firstname= MICHELLE
middlename= j

first name= joe
middlename=E
lastname=lastname

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

Each name is seperate by a space.


Thank you for your help and support

Thank you
newholymanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Will all six string segments always be present?
0
GrahamSkanRetiredCommented:
This puts the names into a two-dimensional array (Names)
Sub splitNamestring()
    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(2, UBound(FullNames))
    
    For i = 0 To UBound(FullNames)
        TempNames = Split(FullNames(i), " ")
        n = UBound(TempNames)
        'first name
        Names(0, i) = TempNames(0)
        'last name
        Names(2, i) = TempNames(n)
        If n = 2 Then 'individual has 3 sub names
            'middle name
            Names(1, i) = TempNames(1)
        End If
    Next i
End Sub

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
Similar to what is posted above
Function from here:http://spreadsheetpage.com/index.php/tip/the_versatile_split_function/

See query16
Database136.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jeffrey CoachmanMIS LiasonCommented:
Results:results
0
GrahamSkanRetiredCommented:
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

Open in new window

0
newholymanAuthor Commented:
There is a space between the lastname and the suffix.

Thak you.
0
newholymanAuthor Commented:
qeury16 gave me this error.
0
newholymanAuthor Commented:
Error in attached file
test-for-fixed.docx
0
Jeffrey CoachmanMIS LiasonCommented:
"qeury16 gave me this error. "

...what error?

In any event here is another version of the same db to test:
Database136.mdb
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
newholymanAuthor Commented:
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
0
GrahamSkanRetiredCommented:
Are any of your comments relevant to my contribution? If not, I'll stop now.
0
newholymanAuthor Commented:
I'm using Boag2000 query examples. Thank you for your help.
0
Jeffrey CoachmanMIS LiasonCommented:
<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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28236751.html#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
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
newholymanAuthor Commented:
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.
0
newholymanAuthor Commented:
When there two firsr name ther always be &.  That is way I can tell that ther two first names
0
GrahamSkanRetiredCommented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
GrahamSkan,

Was you post above meant for this thread?

Jeff
0
newholymanAuthor Commented:
Sorry sir I posted the points on wrong name. How do I change my error
0
Jeffrey CoachmanMIS LiasonCommented:
click the request Attention link and explain what it is that you want to do...
0
GrahamSkanRetiredCommented:
Jeez,  I am confident that I gave good advice, but I thought that you were ignoring me
0
Jeffrey CoachmanMIS LiasonCommented:
I'm a bit confused too...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.