Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting full Names from 1 cell to 2
I deal with an Excel file that has many names on it all in 1 cell.
Some of the common combinations are below. Is there any way to separate the 1 column in to 2 columns bases on table below. FIRST LAST CONVERT TO FIRST LAST FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST FIRST M LAST CONVERT TO FIRST M LAST FIRST M. LAST CONVERT TO FIRST M. LAST FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX FIRST MIDDLE LAST SUFFIX CONVERT TO FIRST MIDDLE LAST SUFFIX FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX The problem I have is that all the names are different lengths. Thanks for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting full Names from 1 cell to 2
The first thing I might so is hilight the cells and go 'Data'....'text to
columns'....choose delimited then click in the 'space' box (unclick tab etc) this should them divide the cells by the space between the names giving you a 1st name or middle name or last name in cells. You'd then have to manipulate them , perhaps by sorting to move columns around so that all the middle names are in the same column and all the last names in the same column. " wrote: I deal with an Excel file that has many names on it all in 1 cell. Some of the common combinations are below. Is there any way to separate the 1 column in to 2 columns bases on table below. FIRST LAST CONVERT TO FIRST LAST FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST FIRST M LAST CONVERT TO FIRST M LAST FIRST M. LAST CONVERT TO FIRST M. LAST FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX FIRST MIDDLE LAST SUFFIX CONVERT TO FIRST MIDDLE LAST SUFFIX FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX The problem I have is that all the names are different lengths. Thanks for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting full Names from 1 cell to 2
the easiest way to do this is to go to Data-Text to Columns and
follow the wizard. this will at least get you all the names in separate columns On Aug 14, 3:09 pm, wrote: I deal with an Excel file that has many names on it all in 1 cell. Some of the common combinations are below. Is there any way to separate the 1 column in to 2 columns bases on table below. FIRST LAST CONVERT TO FIRST LAST FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST FIRST M LAST CONVERT TO FIRST M LAST FIRST M. LAST CONVERT TO FIRST M. LAST FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX FIRST MIDDLE LAST SUFFIX CONVERT TO FIRST MIDDLE LAST SUFFIX FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX The problem I have is that all the names are different lengths. Thanks for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting full Names from 1 cell to 2
this is a slightly different approach... but one thing to consider.
Write a macro to delete all of the suffixes (i.e. find and replace Jr, Sr, etc.). Next use the left and right functions to get the first and last names of the user. to get the first name: =LEFT(A1,FIND(" ",A1)) to get the last name (assumes last name occurs after the last space in the cell) =MID(A1,FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN(" "&A1)- LEN(SUBSTITUTE(" "&A1," ",""))))-1,1000) you might also find this site helpful. http://www.dailydoseofexcel.com/arch...ing-functions/ On Aug 14, 3:33 pm, Tim879 wrote: the easiest way to do this is to go to Data-Text to Columns and follow the wizard. this will at least get you all the names in separate columns On Aug 14, 3:09 pm, wrote: I deal with an Excel file that has many names on it all in 1 cell. Some of the common combinations are below. Is there any way to separate the 1 column in to 2 columns bases on table below. FIRST LAST CONVERT TO FIRST LAST FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST FIRST M LAST CONVERT TO FIRST M LAST FIRST M. LAST CONVERT TO FIRST M. LAST FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX FIRST MIDDLE LAST SUFFIX CONVERT TO FIRST MIDDLE LAST SUFFIX FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX The problem I have is that all the names are different lengths. Thanks for your help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting full Names from 1 cell to 2
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting full Names from 1 cell to 2
I was able to get this solution to work:
Note: Row 1 is column headings so my data (and the related formulas) start on Row 2 Col. A holds the person's name: Tom Jones Tom Jones Jr Tom Jones Sr. Tom Mike Jones Sr. Tom M. Jones Sr. Col. B is a formula to get the first name (returns all the characters up to the first space) =LEFT(A2,FIND(" ",A2)) Col. C is a helper column to determine if there is a middle name: =IF(ISERROR(MID(A2,LEN(B2)+1,FIND(" ",MID(A2,LEN(B2)+1,1024)))),"",MID(A2,LEN(B2)+1,FI ND(" ",MID(A2,LEN(B2)+1,1024)))) This function will return the second name (i.e. characters from the second space to the third space in the string). If there is no 3rd space, then the person has no middle name and the formula returns "" Col. D determines whether Col. C actually returned the middle name or the last name by comparing the value in Col. C to the value in Col. E (the last name - see below) =IF(EXACT(C2,TRIM(F2)),"",C2) Col. E returns the last name less any suffixes. The suffix is determined in Col. F (see below). This formula returns the last name in the string less the suffix (if any) =MID(TRIM(LEFT(A2,LEN(A2)-LEN(F2))),FIND("^^",SUBSTITUTE(" "&TRIM(LEFT(A2,LEN(A2)-LEN(F2)))," ","^^",LEN(" "&TRIM(LEFT(A2,LEN(A2)- LEN(F2))))-LEN(SUBSTITUTE(" "&TRIM(LEFT(A2,LEN(A2)-LEN(F2)))," ",""))))-1+1,1024) Col. F - J determine the suffix. Ron's UDF in the previous post may be a better solution than mine but here's how I did it. Cells G1:J1 include the suffix I want to look for (e.g. G1 = Jr, H1 = Sr, I1 = Jr., J1 = Sr. I put the following formula in cells G2:J2 =IF(ISERROR(FIND(G$1,RIGHT($A2,LEN(G$1)))),"",G$1) this looks for the suffix as the last n characters of the string, with n being the length of the suffix typed in G1:J1. If it matches, then it returns the suffix. If not, it returns blank. Finally, Col. F returns the concatenation of the values in G2:J2 =G2&H2&I2&J2 For example, if the suffix was Jr. then using the values above, G2 = "", H2="", I2="Jr.", J2="" and F2 would = ""&""&"Jr."&"" or "Jr." Finally, in col's K and L, I put the names together as requested in the original post: Col K: First / Middle name =B2&D2 Col L: Last / Suffix =TRIM(E2&" "&F2) If anyone wants to see the excel file, just email me. Tim On Aug 14, 9:06 pm, Ron Rosenfeld wrote: On Tue, 14 Aug 2007 12:09:09 -0700, wrote: I deal with an Excel file that has many names on it all in 1 cell. Some of the common combinations are below. Is there any way to separate the 1 column in to 2 columns bases on table below. FIRST LAST CONVERT TO FIRST LAST FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST FIRST M LAST CONVERT TO FIRST M LAST FIRST M. LAST CONVERT TO FIRST M. LAST FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX FIRST MIDDLE LAST SUFFIX CONVERT TO FIRST MIDDLE LAST SUFFIX FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX The problem I have is that all the names are different lengths. Thanks for your help. The hard part is determining the Suffix. Here's one approach with a UDF. Suffix is a NAME'd cell containing a pipe delimited list of valid Suffixes. For example: Jr|Sr|II|III|IV|MD|PHd <alt-F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. To use this: A1: Full Name B1: =TRIM(SUBSTITUTE(TRIM(A1),C1,"")) C1: =ReExtr(TRIM(A1),"\w+(\s("&Suffix&"))?$") ================================================== ======= Option Explicit Function ReExtr(str As String, sPattern As String) As String 'Requires setting reference to Microsoft VBScript Regular Expressions 5.5 Dim re As RegExp Dim mc As MatchCollection Set re = New RegExp With re .Global = True .IgnoreCase = True .Pattern = sPattern End With If re.Test(str) = True Then Set mc = re.Execute(str) ReExtr = mc(0) End If End Function ===================================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas for Parsing Full names | Excel Worksheet Functions | |||
Resorting full names | Excel Worksheet Functions | |||
how do I find names in a workbook full of names | Excel Discussion (Misc queries) | |||
How do i separate full names appearing in single cells? | Excel Worksheet Functions | |||
Recurring Problems with Links with Full Path Names | Excel Discussion (Misc queries) |