Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use LEN & LEFT on last name to show only 3 letters
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use LEN & LEFT on last name to show only 3 letters
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 ) -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Darlie Mary" 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use LEN & LEFT on last name to show only 3 letters
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 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use LEN & LEFT on last name to show only 3 letters
"M Kan" wrote: 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 ) -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Darlie Mary" 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. The entire last name is in Column C and the first name is in Column A. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use LEN & LEFT on last name to show only 3 letters
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 wrote: The entire last name is in Column C and the first name is in Column A.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to show a list of words by typing a few letters | Excel Discussion (Misc queries) | |||
How can I get a leading zero (ZIP code) to show in a LEFT() calc? | Excel Worksheet Functions | |||
Show a Cell at Top Left | Excel Discussion (Misc queries) | |||
Excel subtotals do not show on left of row numbers | Excel Worksheet Functions | |||
How do I not show certain Letters in a range of cells in excel? | Excel Worksheet Functions |