Seperating First Name

M

magmike

I am using the following to separate the first name out for a greeting
line:

=("Dear " & Left([SentTo],InStr([SentTo]," ")) & ":")

However, this is how it displays "Mike Kline":

Dear Mike :

How do I eliminate that space?
 
M

magmike

I am using the following to separate the first name out for a greeting
line:

=("Dear " & Left([SentTo],InStr([SentTo]," ")) & ":")

However, this is how it displays "Mike Kline":

Dear Mike :

How do I eliminate that space?
Click to expand...

Oh, look - I figured it out! =("Dear " &
Left([SentTo],InStr([SentTo]," ")-1) & ":")

I assumed it must somehow involve a "-1" but wasn't real sure where to
put it. But, if you play with something long enough
_________________________________ .
 
K

Ken Sheridan

Use:

=("Dear " & Left([SentTo],InStr([SentTo]-1," ")) & ":")

However, it would be best to split the data over separate columns in the
table, which you can do using similar expressions in an UPDATE query, using
the InstrRev function to get the last name. If you have some with middle
names or initials you'll have to parse those out as well of course.

Ken Sheridan
Stafford, England
 
J

John W. Vinson

I am using the following to separate the first name out for a greeting
line:

=("Dear " & Left([SentTo],InStr([SentTo]," ")) & ":")

However, this is how it displays "Mike Kline":

Dear Mike :

How do I eliminate that space?
Click to expand...

Just subtract one from the position of the space to get the position before
it:

=("Dear " & Left([SentTo],InStr([SentTo]," ")-1) & ":")
 
Top