Text Formula

C

ChuckL

I have a field that contains an employees first and last
name separated by a space. I need to extract the first
name in one field and the last name in another to create
two additional fields for a first and last name. I can't
use the Left, Right, or Mid formulas b/c the last name
starts at a different position in each occurance. I know
I need to create calculated fields in my query, I just
don't know which formulas to use. Any help would be
greatly appreciated.

Thanks much
 
D

Douglas J. Steele

Actually, you can use Left and Mid.

What's in front of the first space is Left([TotalName], InStr([TotalName], "
") - 1), and what's after the first space is Mid([TotalName],
InStr([TotalName], " ") + 1)

However, how are you going to handle Elly May Clampett?
 
C

ChuckL

Thanks Doug!

I don't have many names with a middle, so I'll just enter
those manually.
-----Original Message-----
Actually, you can use Left and Mid.

What's in front of the first space is Left([TotalName], InStr([TotalName], "
") - 1), and what's after the first space is Mid ([TotalName],
InStr([TotalName], " ") + 1)

However, how are you going to handle Elly May Clampett?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a field that contains an employees first and last
name separated by a space. I need to extract the first
name in one field and the last name in another to create
two additional fields for a first and last name. I can't
use the Left, Right, or Mid formulas b/c the last name
starts at a different position in each occurance. I know
I need to create calculated fields in my query, I just
don't know which formulas to use. Any help would be
greatly appreciated.

Thanks much
Click to expand...


.
Click to expand...
 
A

Alp

Hi Chuck,

I'm no expert to comment on an advice by Doug (hope he won't mind), but;

You might also want to take a look at the following two topics:
- Parsing character separated string into individual components
- Parse one field's data into multiple fields
in http://www.mvps.org/access/ where Dev Ashish has explained them.

Hope it helps.

Alp

ChuckL said:
Thanks Doug!

I don't have many names with a middle, so I'll just enter
those manually.
-----Original Message-----
Actually, you can use Left and Mid.

What's in front of the first space is Left([TotalName], InStr([TotalName], "
") - 1), and what's after the first space is Mid ([TotalName],
InStr([TotalName], " ") + 1)

However, how are you going to handle Elly May Clampett?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a field that contains an employees first and last
name separated by a space. I need to extract the first
name in one field and the last name in another to create
two additional fields for a first and last name. I can't
use the Left, Right, or Mid formulas b/c the last name
starts at a different position in each occurance. I know
I need to create calculated fields in my query, I just
don't know which formulas to use. Any help would be
greatly appreciated.

Thanks much
Click to expand...


.
Click to expand...
Click to expand...
 
Top