How to use LEN & LEFT on last name to show only 3 letters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darlie Mary - 25 Jul 2008 00:49 GMT

Need syntax to show first 3 letters of last name and first letter of first
name. Also if last name only has 2 letters, need to add a Z on end to make 3
letters.

Reply to this Message

M Kan - 25 Jul 2008 00:54 GMT

How are the first and last names separated and do you have any 2-part last
names? Assuming they are in separate cells, the formula would look like this"

A1 = First Name
B1 = Last Name

=IF(LEN(B3)=2,LEFT(B1,3)&"Z",LEFT(B1,3))&LEFT(A1,1)

Signature

Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips

> Need syntax to show first 3 letters of last name and first letter of first
> name. Also if last name only has 2 letters, need to add a Z on end to make 3
> letters.

Reply to this Message

Darlie Mary - 25 Jul 2008 01:21 GMT

> How are the first and last names separated and do you have any 2-part last
> names? Assuming they are in separate cells, the formula would look like this"
[quoted text clipped - 7 lines]
> > name. Also if last name only has 2 letters, need to add a Z on end to make 3
> > letters.

The entire last name is in Column C and the first name is in Column A.

Reply to this Message

Pete_UK - 25 Jul 2008 01:52 GMT

Okay, try this:

=IF(LEN(C2)=2,C2&"Z",LEFT(C2,3))&LEFT(A2,1)

assuming you have a header row so that your data begins in row 2.

Hope this helps.

Pete

On Jul 25, 1:21 am, Darlie Mary <DarlieM...@discussions.microsoft.com>
wrote:

> The entire last name is in Column C and the first name is in Column A.- Hide quoted text -
>
> - Show quoted text -

Reply to this Message

Pete_UK - 25 Jul 2008 00:54 GMT

How is your data organised? First_name and Last_name in separate
cells? First_name and Last_name in one cell separated by a space?
Last_name and First_name in one cell separated by a comma and
space? ... ???

Pete

On Jul 25, 12:49 am, Darlie Mary <Darlie
M...@discussions.microsoft.com> wrote:

> Need syntax to show first 3 letters of last name and first letter of first
> name. Also if last name only has 2 letters, need to add a Z on end to make 3
> letters.

Reply to this Message