Extracting middle initial from text string

[pablofonto]

Hello,

Is there an easy way to split a full name into firstname, middle name and lastname? I have a field name with names such as James R Lowes and i would like to split the name in 3 like.
Firstname : James
Middle Initial: R
Lastname: Lowes

I was able to find a module to extract the first name from a string, but don't know much about VB and cannot figure out to do the rest.

Please help!

thanks,

Pablo

 

[RuralGuy]

If your data is always in that format then the Split() function with a " " space as the delimiter would work great for you.

By the way, how about editing your other thread and Deleting it!

 

[pablofonto]

I don't find the Split function!

 

[Matt Greatorex]

Find the length of the whole name, using Len() (e.g. James R Brown would give 13).

Find the first space, using Instr(), then strip off everything to the left of it to make the First Name.

In the remainder, search for the first space.
Strip off everything to the left of it to make the Middle Name/Initial.

Whatever's left is the Last Name.

 

[RuralGuy]

Pablo,

What version of Access are you using?

 

[pablofonto]

Thank you Matt Greatorex! I will try that and see if I can figure it out.

Ruralguy, I'm using Access 2002

 

[pablofonto]

Matt Greatorex, how to I strip off the right or left of the name? Do I need to combine more than one function into one?

 

[RuralGuy]

Me too! Look in VBA help, not the Access help. While looking at code press F1 and key Split() in the Answer Wizard.

 

[Matt Greatorex]

RuralGuy, I bow to your superior knowledge ;-).

I'm still relatively new to this, so I tend to settle for what I find works, rather than looking for a more efficient way of doing things. Next time I need to do something like this, I'll give the Split function a try.

Apologies if I'm passing on 'weak' advice.

 

[nateobot]

Using the Instr and Mid function:

    Dim FullName As String, FirstName As String, MidName As String, LastName As String
    Dim lSpace As Long, SplitName As Variant
    FullName = "James R Brown"
    'Find the first space
    lSpace = InStr(FullName, " ")
    FirstName = Left$(FullName, lSpace)
    MidName = Mid$(FullName, lSpace + 1, 1)
    'Get the next space
    lSpace = InStr(lSpace + 1, FullName, " ")
    LastName = Right$(FullName, Len(FullName) - lSpace)
    Debug.Print FirstName
    Debug.Print MidName
    Debug.Print LastName

Using Split:

    Dim FullName As String, FirstName As String, MidName As String, LastName As String, SplitName As Variant
    FullName = "James R Brown"

    SplitName = Split(FullName, " ")
    FirstName = SplitName(0)
    MidName = SplitName(1)
    LastName = SplitName(2)
    Debug.Print FirstName
    Debug.Print MidName
    Debug.Print LastName

If you want explanation on any of this please ask.

 

[RuralGuy]

Matt,

It was *not* weak advice at all, just a different way to do it. As you can now see, NateOBot shows how to do it either way.

 

[pablofonto]

Thank you guys! I will try the codes that Nateobot wrote in here and let you know if this solved my issue.

 

[pablofonto]

Thank you guys! I will try the codes that Nateobot wrote in here and let you know if this solved my issue.

One more thing NATEOBOT. Do I paste your code into VBA or I need to create a function in VBA and apply it there? I'm sorry if I'm not asking the correct questions, but I'm not familiar with VBA. I just started to look into it and still pretty new to me.

thanks!

 

[nateobot]

Before walking you through how to run code through VBA, what is your end result for this data going to be?

Is it for display purposes, in which maybe a query would work better?
Are you updating records to record this information for later use, in which again a query would suffice?

If you think a query would work better for your needs then you can modify this one:

SELECT 
    tblNames.FullName, 
    Left([fullname],InStr([fullname]," ")-1) AS FirstName, 
    Mid([fullname],InStr([fullname]," ")+1,1) AS MidName, 
    Right([fullname],Len([fullname])-InStrRev([fullname]," ")) AS LastName
FROM tblNames

 

[pablofonto]

yes, I can use it on an update query and add the results to a table. That would work, let me try the query and I will let you know if I succeed.

Thanks again!

 

[shadow9449]

Pablo:

I hope that the advice that people gave you was helpful.

One of the first rule while creating database tables and determining what the columns should be is that the columns should be non-decomposable, meaning that they should not consist of data that can be broken into smaller bits of data. A name is a good example of this. It would have been better to use 3 columns for FirstName, LastName and Initial than to store them all together and have to deal with how to break them up.

I hope this helps (you or another person designing a database).

SHADOW

 

[pablofonto]

Shadow,

The advises I got worked and was able to resolve the issue. The data I was trying to separate the name from was coming from a third party system which already has the full name all combined, that's why I was trying to split the name.

thanks,

Pablo

 

[pablofonto]

yes, I can use it on an update query and add the results to a table. That would work, let me try the query and I will let you know if I succeed.

Thanks again!

It worked great! Thanks Nateobot.

 

[shadow9449]

Shadow,

The data I was trying to separate the name from was coming from a third party system which already has the full name all combined, that's why I was trying to split the name.

Yes, sometimes inheriting someone else's mistakes can be a nuisance

SHADOW