Formatting help

  • Thread starter Thread starter Mac
  • Start date Start date
M

Mac

Hello,
I have a formula that separates the first and last name in a cell. It works
for the last name but does not work for the first name, I either get a
partial first name or I get the first & part of the last. A nice person by
the name of Pete helped me yesterday. I have pasted the formula I am using.
Thank you for any help you can vgive me.

=LEFT('[package.xls]Highest Paid Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid Employee'!$C$18))
 
I see two problems.

1. You have "Packagae" rather than "Package" in the Find function.

2. There should be a space between the double quotes in the Find function.

Try:

=LEFT('[package.xls]Highest Paid Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND(" ",'[Package.xls]Highest Paid Employee'!$C$18))

Regards
Fred
 
Thak you Fred,

I made the changes and when I put the space in the " " I got only 4 letters
of the first name on one and the next one I got first name and first letter
of last name. Any ideas will be greatly appreciated.

Fred Smith said:
I see two problems.

1. You have "Packagae" rather than "Package" in the Find function.

2. There should be a space between the double quotes in the Find function.

Try:

=LEFT('[package.xls]Highest Paid Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND(" ",'[Package.xls]Highest Paid Employee'!$C$18))

Regards
Fred

Mac said:
Hello,
I have a formula that separates the first and last name in a cell. It
works
for the last name but does not work for the first name, I either get a
partial first name or I get the first & part of the last. A nice person by
the name of Pete helped me yesterday. I have pasted the formula I am
using.
Thank you for any help you can vgive me.

=LEFT('[package.xls]Highest Paid Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
Employee'!$C$18))
Click to expand...
Click to expand...
 
What does the data look like? Give us examples of the problems.

Regards,
Fred

Mac said:
Thak you Fred,

I made the changes and when I put the space in the " " I got only 4
letters
of the first name on one and the next one I got first name and first
letter
of last name. Any ideas will be greatly appreciated.

Fred Smith said:
I see two problems.

1. You have "Packagae" rather than "Package" in the Find function.

2. There should be a space between the double quotes in the Find
function.

Try:

=LEFT('[package.xls]Highest Paid
Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND(" ",'[Package.xls]Highest Paid
Employee'!$C$18))

Regards
Fred

Mac said:
Hello,
I have a formula that separates the first and last name in a cell. It
works
for the last name but does not work for the first name, I either get
a
partial first name or I get the first & part of the last. A nice person
by
the name of Pete helped me yesterday. I have pasted the formula I am
using.
Thank you for any help you can vgive me.

=LEFT('[package.xls]Highest Paid
Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
Employee'!$C$18))
Click to expand...
Click to expand...
Click to expand...
 
Hi Fred,

Thank you for getting back to me. The name on one is Barry Zeltzer When I
use the formula below I get the last oka but the first name comes out as
Barry Z. On the next which is Paula Gonsalves i get last name ok but the
first name comes out as Paula Gonzale. What could I be doing wrong. Again
thank you for you help.

=LEFT('[Package.xls]Highest Paid Employee'!$C$16,LEN('[Package.xls]Highest
Paid Employee'!$C$16)-FIND(" ",'[Package.xls]Highest Paid Employee'!$C$16))


Fred Smith said:
What does the data look like? Give us examples of the problems.

Regards,
Fred

Mac said:
Thak you Fred,

I made the changes and when I put the space in the " " I got only 4
letters
of the first name on one and the next one I got first name and first
letter
of last name. Any ideas will be greatly appreciated.

Fred Smith said:
I see two problems.

1. You have "Packagae" rather than "Package" in the Find function.

2. There should be a space between the double quotes in the Find
function.

Try:

=LEFT('[package.xls]Highest Paid
Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND(" ",'[Package.xls]Highest Paid
Employee'!$C$18))

Regards
Fred

Hello,
I have a formula that separates the first and last name in a cell. It
works
for the last name but does not work for the first name, I either get
a
partial first name or I get the first & part of the last. A nice person
by
the name of Pete helped me yesterday. I have pasted the formula I am
using.
Thank you for any help you can vgive me.

=LEFT('[package.xls]Highest Paid
Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
Employee'!$C$18))
Click to expand...
Click to expand...
Click to expand...
 
Hi Mac,

I put "John Smith" (without the quotes) in A1 and this formula in B1:

=LEFT(A1,LEN(A1)-FIND(" ",A1))

and I got John in B1, as expected because the formula is equivalent to
yours - you just happen to be looking in different files/sheets. If
you have extra spaces in the text, however, then you will get
different results. For example, " John Smith" in A1 (one space at the
beginning) will give me " John Smit" in B1.

So, I would suggest that you look at your data carefully and remove
any extra spaces - you can use the TRIM function to do this.

Incidentally, the formula returns the space at the end of the first
name, so it would be better as:

=LEFT(A1,LEN(A1)-FIND(" ",A1)-1)

so you can put -1 near the end of your formula (between the last two
brackets).

Hope this helps.

Pete

Hi Fred,  

Thank you for getting back to me. The name on one is Barry Zeltzer  WhenI
use the formula below I get the last  oka but the first name comes out as
Barry Z.  On the next which is Paula Gonsalves i get last name ok but the
first name comes out as Paula Gonzale.  What could I be doing wrong.  Again
thank you for you help.

=LEFT('[Package.xls]Highest Paid Employee'!$C$16,LEN('[Package.xls]Highest
Paid Employee'!$C$16)-FIND(" ",'[Package.xls]Highest Paid Employee'!$C$16))



Fred Smith said:
What does the data look like? Give us examples of the problems.
Regards,
Fred
Click to expand...

Mac said:
Thak you Fred,
I made the changes and when I put the space in the " " I got only 4
letters
of the first name on one and the next one I got first name and first
letter
of last name.  Any ideas will be greatly appreciated.
:
I see two problems.
1. You have "Packagae" rather than "Package" in the Find function.
2. There should be a space between the double quotes in the Find
function.
Try:
=LEFT('[package.xls]Highest Paid
Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND(" ",'[Package.xls]Highest Paid
Employee'!$C$18))
Regards
Fred
Hello,
I have a formula that separates the first and last name in a cell.  It
works
for the last name  but does not work for the first name,  I either get
a
partial first name or I get the first & part of the last. A nice person
by
the name of Pete helped me yesterday.  I have pasted the formula I am
using.
Thank you for any help you can vgive me.
=LEFT('[package.xls]Highest Paid
Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
Employee'!$C$18))- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
The formula you have was designed to give you the last name, but it should
be using the Right function, rather than Left.

So the proper formulas are:

Last Name: =right(c16,len(c16)-find(" ",c16))
First Name: =left(c16,find(" ",c16)-1)

I'll let you substitute the full cell address in each case.

Sorry I didn't pick this up the first time.

Regards,
Fred.

Mac said:
Hi Fred,

Thank you for getting back to me. The name on one is Barry Zeltzer When I
use the formula below I get the last oka but the first name comes out as
Barry Z. On the next which is Paula Gonsalves i get last name ok but the
first name comes out as Paula Gonzale. What could I be doing wrong.
Again
thank you for you help.

=LEFT('[Package.xls]Highest Paid Employee'!$C$16,LEN('[Package.xls]Highest
Paid Employee'!$C$16)-FIND(" ",'[Package.xls]Highest Paid
Employee'!$C$16))


Fred Smith said:
What does the data look like? Give us examples of the problems.

Regards,
Fred

Mac said:
Thak you Fred,

I made the changes and when I put the space in the " " I got only 4
letters
of the first name on one and the next one I got first name and first
letter
of last name. Any ideas will be greatly appreciated.

:

I see two problems.

1. You have "Packagae" rather than "Package" in the Find function.

2. There should be a space between the double quotes in the Find
function.

Try:

=LEFT('[package.xls]Highest Paid
Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND(" ",'[Package.xls]Highest Paid
Employee'!$C$18))

Regards
Fred

Hello,
I have a formula that separates the first and last name in a cell.
It
works
for the last name but does not work for the first name, I either
get
a
partial first name or I get the first & part of the last. A nice
person
by
the name of Pete helped me yesterday. I have pasted the formula I
am
using.
Thank you for any help you can vgive me.

=LEFT('[package.xls]Highest Paid
Employee'!$C$18,LEN('[Package.xls]Highest
Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
Employee'!$C$18))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top