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

 

[nateobot]

Are you doing this in a query or VBA?

You should research the Mid, InStr, and InStrRev functions.

Also you should think about how you will handle people with no middle initial and people with two last names.

 

[pablofonto]

I found a code in VBA to extract the first name.
Firstname=Left(Contactname,InStr(1,Contactname," ")) and I was able to use in a query as well. I will check those other functions you mentioned and see if I can figure it out. In this table that I'm working on everyone has a first name, middle initial and lastname.

Thanks,

Pablo

 

[dsfcom]

Hello. I've learned a bit about breaking down text strings today but am still having some trouble with splitting a text field using a query. My field contains either [LASTNAME] [FIRSTNAME] [MI] or [LASTNAME] [FIRSTNAME] combinations. What I'm trying to do is break down the field into three separate fields (i.e. [FIRSTNAME], [LASTNAME], and [MI]).

Example field value: "FISHER DONALD A"

I'm using this to get the last name on it's own "FISHER": Left([NAME],InStr(1,[NAME]," ")-1)

And this gives me the first name and middle initial "DONALD A": Right(Trim([NAME]),Len(Trim([NAME]))-InStr(1,[NAME]," "))

But how can I break down the first name and middle initial? And how can I avoid #Error when there is no middle initial?

I appreciate any help!

 

[gemma-the-husky]

what if there's no middle name
then what?

 

[dsfcom]

That question was also in my post...any offers for a solution?

This works to get the first name only from the [FIRSTNAME] [MI] combination even if there is no middle initial:

FIRSTNAME: IIf(Left([FIRSTNAMEMI],(InStr(1,[FIRSTNAMEMI]," ")))=" ",[FIRSTNAMEMI],Left([FIRSTNAMEMI],(InStr(1,[FIRSTNAMEMI]," "))))

This works to get the middle initial only from the [FIRSTNAME] [MI] combination when there is one and doesn't error out when there isn't one:

MI: IIf(Left([FIRSTNAMEMI],(InStr(1,[FIRSTNAMEMI]," ")))=" ","",Right([FIRSTNAMEMI],1))

I've ended up using two queries; one to break out the last name and one to break out the first name and middle initial. If anyone knows how to do all this at one time I'd love to hear from you!