Formatting help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mac - 06 Mar 2008 21:22 GMT

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

Reply to this Message

Fred Smith - 06 Mar 2008 22:24 GMT

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
[quoted text clipped - 8 lines]
> Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
> Employee'!$C$18))

Reply to this Message

Mac - 07 Mar 2008 13:30 GMT

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.
>
[quoted text clipped - 22 lines]
> > Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
> > Employee'!$C$18))

Reply to this Message

Fred Smith - 07 Mar 2008 14:08 GMT

What does the data look like? Give us examples of the problems.

Regards,
Fred

> Thak you Fred,
>
[quoted text clipped - 36 lines]
>> > Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
>> > Employee'!$C$18))

Reply to this Message

Mac - 07 Mar 2008 15:40 GMT

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

> What does the data look like? Give us examples of the problems.
>
[quoted text clipped - 41 lines]
> >> > Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
> >> > Employee'!$C$18))

Reply to this Message

Pete_UK - 07 Mar 2008 16:17 GMT

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,
>
[quoted text clipped - 54 lines]
>
> - Show quoted text -

Reply to this Message

Fred Smith - 08 Mar 2008 18:24 GMT

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.

> Hi Fred,
>
[quoted text clipped - 58 lines]
>> >> > Paid Employee'!$C$18)-FIND("",'[Packagae.xls]Highest Paid
>> >> > Employee'!$C$18))

Reply to this Message