Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated text to column
My imported "Name" column (column A) contains different name formats: First
Name Last Name; First Name Middle Initial Last Name; and, in some cases, First Name Middle Name Last Name (for example: Mary Smith, John B. Doe, William James Burrows). Is there a simple way to convert all names to three columns with the Given Name in Column B, the Middle Initial or Name in Column C, and the last name in Column D? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated text to column
As your data is not Consistence - there might be a way around with formulas
that brake the names into 3 parts but I would suggest that you start by examining "Text to Columns" fro the DATA menu Micky "dfw" wrote: My imported "Name" column (column A) contains different name formats: First Name Last Name; First Name Middle Initial Last Name; and, in some cases, First Name Middle Name Last Name (for example: Mary Smith, John B. Doe, William James Burrows). Is there a simple way to convert all names to three columns with the Given Name in Column B, the Middle Initial or Name in Column C, and the last name in Column D? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated text to column
Based initially on Chip Pearson's solution for names in the "last, First M."
format, I came up with these. Assume name in A2, in B2, use this formula: =LEFT(A2,IF(ISERROR(FIND(" ",A2,1)),LEN(A2),FIND(" ",A2,1)-1)) In C2 use this formula (remember, all one line) =IF(ISERR(FIND(" ",A2,FIND(" ",A2)+1)),"",TRIM(IF(ISERROR(FIND(" ",A2,1)),A2,MID(A2,FIND(" ",A2,1)+1,IF(ISERROR(FIND(" ",A2,FIND(" ",A2,1)+2)),LEN(A2),FIND(" ",A2,FIND(" ",A2,1)+2))-FIND(" ",A2,1))))) In D2 use this formula, again remember, all 1 line: =IF(C2<"",TRIM(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" ",A2,FIND(" ",A2,FIND(" ",A2,1)+2))),LEN(A2),FIND(" ",A2,FIND(" ",A2,FIND(" ",A2,1)+2))-1))),TRIM(IF(ISERROR(FIND(" ",A2,1)),A2,MID(A2,FIND(" ",A2,1)+1,IF(ISERROR(FIND(" ",A2,FIND(" ",A2,1)+2)),LEN(A2),FIND(" ",A2,FIND(" ",A2,1)+2))-FIND(" ",A2,1))))) "dfw" wrote: My imported "Name" column (column A) contains different name formats: First Name Last Name; First Name Middle Initial Last Name; and, in some cases, First Name Middle Name Last Name (for example: Mary Smith, John B. Doe, William James Burrows). Is there a simple way to convert all names to three columns with the Given Name in Column B, the Middle Initial or Name in Column C, and the last name in Column D? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated text to column
On Wed, 9 Dec 2009 12:01:03 -0800, dfw wrote:
My imported "Name" column (column A) contains different name formats: First Name Last Name; First Name Middle Initial Last Name; and, in some cases, First Name Middle Name Last Name (for example: Mary Smith, John B. Doe, William James Burrows). Is there a simple way to convert all names to three columns with the Given Name in Column B, the Middle Initial or Name in Column C, and the last name in Column D? If, in fact, you always have either two or three space separated words to parse, then it is relatively simple to do this with a VBA Macro. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), first select the range to parse. Then <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. =================================== Option Explicit Sub ParseNames() 'Acceptable formats: ' FN MN LN ' FN MI LN ' FN LN Dim c As Range Dim rg As Range Dim sFullName As Variant Set rg = Selection 'various ways to set this For Each c In rg sFullName = Split(c.Value, " ") c.Offset(0, 1).Value = sFullName(0) c.Offset(0, 3).Value = sFullName(UBound(sFullName)) If UBound(sFullName) = 2 Then c.Offset(0, 2).Value = sFullName(1) End If Next c End Sub ==================================== If you need to do this with formulas, then B1: =LEFT(A1,FIND(" ",A1)-1) C1: =IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"", TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99))) D1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) ------------------------------- However, if your names do not fall precisely into those formats, then these methods will not work reliably. It may be possible, or not, depending on the real variety in your name formats. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated text to column
Hi,
I came up with the following approach, which will work even if there is more than one middle initial, middle name, or any combination thereof. If the name is in A2, enter the following formulas in B2, C2, and D2. in B2, =LEFT(A2,FIND(" ",A2)-1) In C2, =IF(LEN(TRIM(A2))=LEN(B2)+LEN(D2)+1," ",MID(TRIM(A2),LEN(B2)+2,LEN(TRIM(A2))-LEN(B2)-LEN(D2)-2)) In D2, =RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND("#",SUBSTITUTE(TRIM(A2)," ","#",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))) Note: The formula in C2 will work only after the formula in D2 is also entered. One issue with my formulas: If the last name ends with a suffix such as "Sr", "Jr" or a roman numeral such as "II" or "III" (with a space between the name and the suffix), the formulas would misinterpret the middle and last names and wrongly return only the suffix as the last name (i.e., anything after the last space in the entire name). Secondly, there should be a space between an intital and a name, a name and an initial, or two initials. Thus "William J. Burrows" will work but not "William J.Burrows" Please click "Yes" if this is helpful. Best regards, B. R. Ramachandran "dfw" wrote: My imported "Name" column (column A) contains different name formats: First Name Last Name; First Name Middle Initial Last Name; and, in some cases, First Name Middle Name Last Name (for example: Mary Smith, John B. Doe, William James Burrows). Is there a simple way to convert all names to three columns with the Given Name in Column B, the Middle Initial or Name in Column C, and the last name in Column D? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a text from a column in a text string within another column? | New Users to Excel | |||
complicated multi column formula help | Excel Discussion (Misc queries) | |||
Forming a binary column from a more complicated column of diagnostic codes | Excel Discussion (Misc queries) | |||
Complicated extraction of text | Excel Discussion (Misc queries) | |||
Wrap text in column headers to fit text in column | Excel Discussion (Misc queries) |