name extraction

[floridagal]

I have a list of names like this:
Smith, Tom A (Tom Smith)
Jones, Bill (Bill Jones)
White, Lucie A (Lucie White)

Can anyone tell me how to extract out the first and last
name?

TIA!

 

[Rick B]

If you will search, this was just answered earlier today.

Please don't post without searching first.


Rick B

 

[Rick B]

The following link will get you several posts including the one from
today...

http://groups-beta.google.com/groups?q=microsoft.public.access+lastname&start=0&scoring=d&


Rick B

 

[Ken Snell [MVP]]

Are the first and last names always within the parentheses?

Or did you do that just to show what you want?

 

[Guest]

Hi Ken
The names are in the exact format with the parenthesis. I
saw the previous post Rick B referred to, but that is a
simple last name first name no punctuation, I need to
extract out of the format as below. Any help would be
appreciated.

Thanks!

 

[Ken Snell [MVP]]

If you want the first and last name together:

FirstLast = Mid(Left([FullNameField], Len([FullNameField]) - 1),
InStr([FullNameField], "(") + 1)


If you want just the first name:

FirstOnly = Mid(Left([FullNameField], InStrRev([FullNameField]), " ") - 1),
InStr([FullNameField], "(") + 1)


If you want just the last name:

FirstOnly = Mid(Left([FullNameField], Len([FullNameField]) - 1),
InStrRev([FullNameField], " ") + 1)

 

[Guest]

Thank you Ken! Works like a charm!!

-----Original Message-----
If you want the first and last name together:

FirstLast = Mid(Left([FullNameField], Len ([FullNameField]) - 1),
InStr([FullNameField], "(") + 1)


If you want just the first name:

FirstOnly = Mid(Left([FullNameField], InStrRev ([FullNameField]), " ") - 1),
InStr([FullNameField], "(") + 1)


If you want just the last name:

FirstOnly = Mid(Left([FullNameField], Len ([FullNameField]) - 1),
InStrRev([FullNameField], " ") + 1)

--

Ken Snell
<MS ACCESS MVP>

Hi Ken
The names are in the exact format with the parenthesis. I
saw the previous post Rick B referred to, but that is a
simple last name first name no punctuation, I need to
extract out of the format as below. Any help would be
appreciated.

Thanks!

.