Extrat LastName, FirstName MI in a query from a FullName Field

[ThereUare]

I'm having a brain fart righ now. I can only do this to extract the First Name, but I also get the Middle Initial. I need something to only extract the First Name. Please Help. This is my formula so far.

Formula: (Used in a Query)
FName: Mid([FullName],InStr([FullName]," ")+1)

 

[straybullet]

I believe that the +1 is why it's retrieving the middle initial. Have you tried substituting -1?

Let them hate - so long as they fear... Lucius Accius

 

[mintjulep]

Doesn't MID expect three arguments?

MID(string,start,#ofchars)

You are only giving it 2.

 

[straybullet]

yes actually it does, try LEFT

Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

grr, we need an edit button...



here we go: Left([fullname],InStr([FullName]," ")-1)

Let them hate - so long as they fear... Lucius Accius

 

[mintjulep]

I'm wondering if the # of chars arg is omitted it just goes to the end of string?

I think you need:

Mid([FullName],InStr([FullName]," ")+1, Len([FullName])-2)

 

[ThereUare]

Ok well I thank you all for your inputs but none of them worked. It will definitly have to be a Mid and InStr but I need to be able to see the space between the FirstName and the Middle Initial. This is the format I need to break up.
LastName, FirstName MI
I was able to extract the first name but it also takes the middle initial. Some names in my table do not have any middle initials so it will have to look for a space. Hope that clarifies what I need.

 

[xlbo]

As per Mintjulep - the reason your 1st formula doesn;t work is that you are not specifying where in the string to stop so you get all characters from the 1st space to the end

This will not be easy - especially if you don;t know whether there is going to be a middle initial or not

Here is a starter for 10

Find the 1st space:

=instr([Full Name]," ")

Find the 2nd space:

=instr(instr([Full Name]," ")+1,[Full Name]," ")

Your MID formula requires 3 arguments

Text to use
Start position
No. Of characters

You should be able to use the 2 formulae above to get the start position & no. of characters for each element of the name you need to extract. You will also need to use an IIF statement to test for if there is a Middle Initial (ie whether there is a 2nd space or not)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Consider just using Text to Columns (Data > Text To Columns) with SPACE as the delimiter.

One more complication, though: What if someone has a last name like Van Wagner? Or De Los Santos? Hopefully you have few enough records that you can spot check for those.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[HarleyQuinn]

Might be a bit of overkill (but it's a short solution non the less) but this is fairly easy with a UDF.

In a module paste:

Public Function SplitFirstName(strOriginal As String) As String
SplitFirstName = Split(Split(strOriginal, ", ")(1))(0)
End Function

Call it as you would a normal function

=SplitFirstName()

Like I say, overkill probably for this task but what the hell eh?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AnotherHiggins]

I had actually forgotten about this when I last posted - though I still think Text-To-Columns is probably the easiest way to go - but Chip Pearson has some hefty formulas that will handle even complex names:

From

http://www.cpearson.com/excel/FirstLast.htm:

Last Name:
[COLOR=blue white]=LEFT(A2, IF(ISERROR(FIND(",", A2, 1)), LEN(A2), FIND(",", A2, 1) - 1))[/color]

First Name:
[COLOR=blue white]=TRIM(IF(ISERROR(FIND(",", A2, 1)), A2, MID(A2, FIND(",", A2, 1) + 1, IF(ISERROR(FIND(" ", A2, FIND(",", A2, 1) + 2)), LEN(A2), FIND(" ", A2, FIND(",", A2, 1) + 2)) - FIND(",", A2, 1))))[/color]

Middle Name:
[COLOR=blue white]=TRIM(RIGHT(A2, LEN(A2) - IF(ISERROR(FIND(" ", A2, FIND(" ", A2, FIND(",", A2 , 1) + 2))), LEN(A2), FIND(" ", A2, FIND(" ", A2, FIND(",", A2, 1) + 2)) - 1)))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Would suggest that given the syntax provided, this is in an Access query chaps...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HarleyQuinn]

If it's Access I'm definitely going to go for the UDF I posted (but I like UDF's ), it'll still work if you take the = off the call

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AnotherHiggins]

er.... I could've sworn I read the OP, but looking back I don't know how I missed that. Thanks for the nudge, Geoff.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.