name extraction

F

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!
 
R

Rick B

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

Please don't post without searching first.


Rick B
 
K

Ken Snell [MVP]

Are the first and last names always within the parentheses?

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

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!
 
K

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)
 
G

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!
Click to expand...


.
Click to expand...
 
Top