| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract letters from a cell
Hi,
We received a list of names of persons we need to sent letters to. The last name is okay in a separate cell. The first , 2nd (and 3rd) names are all in one cell, but we want to extract only the initials. The cells are not consistent as some cells have the full first name and then 2nd and/or 3rd initial(s), some also have the full 2nd name. In worst case scenario I thought of only extracting the initial of the first name. Examples: Diane M. needs to be DM Laura Mary needs to be LM Laura Mary C. needs to be LMC If any of you can help me it will be great. Thanks, Lupe |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract letters from a cell
One possibility would be to use the text to columns function to get these
names into separate columns and then run the LEFT function to extract the first character. Example: Billy Bob Thornton becomes Billy | Bob | Thornton where | indicates a new column. Assume these words are in A1, B1, C1 =LEFT(A1,1) returns B =LEFT(B1,1) returns B =LEFT(C1,1) returns T =CONCATENATE(LEFT(A1,1)," ",LEFT(B1,1)," ",LEFT(C1,1)) returns B B T Does that help? Dave -- Brevity is the soul of wit. "Lupe" wrote: Hi, We received a list of names of persons we need to sent letters to. The last name is okay in a separate cell. The first , 2nd (and 3rd) names are all in one cell, but we want to extract only the initials. The cells are not consistent as some cells have the full first name and then 2nd and/or 3rd initial(s), some also have the full 2nd name. In worst case scenario I thought of only extracting the initial of the first name. Examples: Diane M. needs to be DM Laura Mary needs to be LM Laura Mary C. needs to be LMC If any of you can help me it will be great. Thanks, Lupe |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract letters from a cell
Dave,
Thanks very much. It works! I don't know how you guys do it, but you are all of great help!! Lupe "Dave F" wrote: One possibility would be to use the text to columns function to get these names into separate columns and then run the LEFT function to extract the first character. Example: Billy Bob Thornton becomes Billy | Bob | Thornton where | indicates a new column. Assume these words are in A1, B1, C1 =LEFT(A1,1) returns B =LEFT(B1,1) returns B =LEFT(C1,1) returns T =CONCATENATE(LEFT(A1,1)," ",LEFT(B1,1)," ",LEFT(C1,1)) returns B B T Does that help? Dave -- Brevity is the soul of wit. "Lupe" wrote: Hi, We received a list of names of persons we need to sent letters to. The last name is okay in a separate cell. The first , 2nd (and 3rd) names are all in one cell, but we want to extract only the initials. The cells are not consistent as some cells have the full first name and then 2nd and/or 3rd initial(s), some also have the full 2nd name. In worst case scenario I thought of only extracting the initial of the first name. Examples: Diane M. needs to be DM Laura Mary needs to be LM Laura Mary C. needs to be LMC If any of you can help me it will be great. Thanks, Lupe |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| letters cut off in cell | Excel Worksheet Functions | |||
| Please help: Extract some information from a cell | Excel Discussion (Misc queries) | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| separating numbers and letters from alphanumeric cell contents | Excel Worksheet Functions | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Linear Mode
