Another Method for Parsing Names

Having bothered others with ways of parsing names, I thought I'd at lease 
contribute a simple solution for doing the complete job...

Assume you have names of the following types in column C

     Mrs Dorothy Hannity

     Dr P R Rogers

     Dana Delany

     Mr Bradley K Pitts

Type the following formulas into the specified cells:

      O1=FIND(" ",C1)
     Determines the location/existence of the blank following the Salutaton 
or First Name

      P1=FIND(" ",C1,FIND(" ",C1)+1)
     Determines the location/existence of the blank following the First Name 
or Middle Initial(MI)

      Q1=FIND(" ",R1)
     Determines the location/existence of the blank following the Middle 
Initial in the next, adjacent cell

      R1=IF(ISERROR(P1),RIGHT(C1,LEN(C1)-O1),RIGHT(C1,LEN(C1)-P1))
     Defines the Last Name or MI/LN if there is a MI

      S1=LEFT(C1,O1-1)
     Creates the Salutation column

      T1=IF(ISERROR(S1)," ",MID(C1,O1+1,P1-O1))
     Creates the First Name column

      U1=IF(FIND(" ",R1)=2,LEFT(R1,1),"")
     Creates the MI column

      V1=IF(U1<>"",MID(R1,FIND(" ",R1)+1,99),R1)
     Creates the Last Name column


Fill the entries down and then copy the resulting values into another set of 
columns.

 It works for all variables except for names with more than one middle 
initial.

0
jazzzbo (19)
10/18/2007 8:21:57 PM
excel 39879 articles. 2 followers. Follow

1 Replies
782 Views

Similar Articles

[PageSpeed] 23

> It works for all variables except for names with more than one middle 
> initial.

Jr., Sr., III, Esq., Phd, MBA, CEO....





"Jim Berglund" <jazzzbo@shaw.ca> wrote in message 
news:811E3DD6-D1FF-4904-841C-348FE6F0A54C@microsoft.com...
> Having bothered others with ways of parsing names, I thought I'd at lease 
> contribute a simple solution for doing the complete job...
>
> Assume you have names of the following types in column C
>
>     Mrs Dorothy Hannity
>
>     Dr P R Rogers
>
>     Dana Delany
>
>     Mr Bradley K Pitts
>
> Type the following formulas into the specified cells:
>
>      O1=FIND(" ",C1)
>     Determines the location/existence of the blank following the Salutaton 
> or First Name
>
>      P1=FIND(" ",C1,FIND(" ",C1)+1)
>     Determines the location/existence of the blank following the First 
> Name or Middle Initial(MI)
>
>      Q1=FIND(" ",R1)
>     Determines the location/existence of the blank following the Middle 
> Initial in the next, adjacent cell
>
>      R1=IF(ISERROR(P1),RIGHT(C1,LEN(C1)-O1),RIGHT(C1,LEN(C1)-P1))
>     Defines the Last Name or MI/LN if there is a MI
>
>      S1=LEFT(C1,O1-1)
>     Creates the Salutation column
>
>      T1=IF(ISERROR(S1)," ",MID(C1,O1+1,P1-O1))
>     Creates the First Name column
>
>      U1=IF(FIND(" ",R1)=2,LEFT(R1,1),"")
>     Creates the MI column
>
>      V1=IF(U1<>"",MID(R1,FIND(" ",R1)+1,99),R1)
>     Creates the Last Name column
>
>
> Fill the entries down and then copy the resulting values into another set 
> of columns.
>
> It works for all variables except for names with more than one middle 
> initial.
> 


0
10/18/2007 7:00:39 PM
Reply:

Similar Artilces:

matching a column of numbers to another in another spreadsheet
I have a spreadsheet of shortpayment amounts and their invoice numbers on one spreadsheet with a column of credit amounts with their invoice numbers on another spreadsheet. I need to match the credits written with the shortpayments. kinda like this spreadsheet 1 spreadsheet 2 inv # amount date Credit # Amount Date 122334 15.00 11-25-09 675555 15.00 12-10-09 223345 22.00 10-20-09 754444 22.00 111-25-09 naturally...

Acessing function with a DLL which has be loaded from another dll
I have A.dll and B.dll. Assume that A.dll has following functions: A1() A2() Assume that B.dll has following functions: B1() B2() I am loading B.dll from function A1() in A.dll.After Loading B.dll i make a call to B1().After executing the call the function returns to beginning of A2.dll. why is this so? Define "begining". The word tends to make no sense in this context. What you seem to be saying is that you have void A1() { HANDLE h = ::LoadLibrary(_T("B.DLL")); ...test for h==NULL, deal with error typedef void (*B1)() B1Proc; B1Proc B1; ...

Grabbing a number from another cell on another worksheet
Hello, I have a spreadsheet with a number of different worksheets. All I want to do is if I enter a number into a cell on the 2nd worksheet for example, I want it to automatically show in a specific cell on the first worksheet. So a simple copy and paste but for excel to automatically do it for me! it seems like something so easy that I just cant figure out how to do. Try this... Do this once. Select the cell on the 2nd sheet Right click>Copy Navigate to the other sheet and select the cell where you want the value to appear. Right click>Paste Special>Paste Lin...

Creating column content based on another column's keyword(s) #2
Max, That scriptlet was very useful, and easily tweakable to work in myria applications. Thx! -K -- KHashmi31 ----------------------------------------------------------------------- KHashmi316's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1043 View this thread: http://www.excelforum.com/showthread.php?threadid=27348 Glad to hear that ! Thanks for posting back -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "KHashmi316" <KHashmi316.1evevz@excelforum-nospam.com> wrote in message news:KHashmi316.1e...

Inserting Auto Text
Operating System: Mac OS X 10.6 (Snow Leopard) I want to insert the File Name of a document in the footer. I click on Insert, Auto Text and it only brings up Auto Text. It does NOT bring up all the wonderful PAGE possibilities that are shown on the video. HELP!!! <br><br>Mary Solomon At the very least we need to know what version of the program -- Word, I assume -- you're using & preferably your current update level. Regards |:>) Bob Jones [MVP] Office:Mac On 4/18/10 9:44 AM, in article 59bb7156.-1@webcrossing.JaKIaxP2ac0, "marysolomon@officef...

Populate data in cell by looking at another cells data
Hi Everyone, Hope I find you well. I'm not even sure how to go about this, so I hope that someone can shed some light. I have a serial number in one cell eg '80199DD270238' where 'DD' is code for another value, in this case 'DD' = 'BLUE'. How can I automatically populate a cell with the value 'BLUE' by looking at the serial number. Many thanks for any help you can provide. Best Regards Gazza Hi if the characters are alsways at the same place use =IF(MID(A1,6,2)="DD","Blue","other color") if they could be at ...

Sum dependant on '1' value in another column
Hi, Let's say I have a '1' or nothing in column 'L' and a time in seconds in column 'B'. The '1' means the time is a legitimate one. Can I do a sum of all the 'legitimate' times in column 'B' based on whether there is a corresponding '1' in column 'L'? Is there a formula that will allow me to do that? Sorry for the ignorance of the question but it is late and I am struggling to make this spreadsheet work for me. Thanks in advance, Dave Hi DL, Look at the SUMIF function in Excel help. Try: =SUMIF(L1:L10...

is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? #2
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? ...

Worksheet Names (Revised)
I went ahead and included an updated code from a previous post with a comment line above the problem area. I can't get the code to work. I need it to populate a combobox list with the sheet names of a specified workbook. Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim fs, f, f1, s, sf, sh As Worksheet, wbkChart As Workbook Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder("c:\SymmcoPrograms\" & TextBox1.Value & "\" & ComboBox1.Value) Set sf = f.Files If fs.FileExists("c:\Symm...

Official name of the File Selection Part of CFileDialog
I want to know the official name of the file selection portion of the CFileDialog. I also want to know how this is implemented, is there a special kind of ListBox that can do multiple columns? "Peter Olcott" <NoSpam@SeeScreen.com> wrote in message news:Kw%3h.10768$IC.9812@newsfe21.lga... >I want to know the official name of the file selection portion of the >CFileDialog. I also want to know how this is implemented, is there a >special kind of ListBox that can do multiple columns? List-View http://msdn.microsoft.com/library/en-us/shellcc/platform/commctls/list...

sumproduct of 2 columns with date and name
This is the formula I am using but it doesn't work. any suggestions? I am trying to add the number of times that in one column it says any date of january and in the other column it says Tom. Not counting any blank rows in the date column. =SUMPRODUCT(--(TEXT($J$4:$J$97,"mmm")="jan"),--($J$4:$J$97<>"")*($O$4:$O$97="tom")) Why do you say it doesn't work? excel guru i''m not wrote: > > This is the formula I am using but it doesn't work. > > any suggestions? > > I am trying to add the number of times ...

Automatic Link to another workbook
I have a workbook created that, every time I open it, I get the question: Do you want to update the automatic link to data in another workbook? I didn't consciously set up a link and I don't want to get this message. How do I find this link and break it so I will not continue to get this message? Any help is appreciated. Close any other Excel workbooks you have open and do a "Find" for :\ (colon backslash). If you have several worksheets in your workbook, ask Excel to search the entire workbook by selecting find within "Workbook" instead of "Sheet&quo...

Problem with #name
Dear All , I am having a problem with the excel. In column 2, I have a set of data which consists of two data 1) "- Cardburry Taste Good (C)" 2) "- Cardburry Taste Bad (M)" 3) "- Cardburry Taste Moderate (K)" When I open the csv files in excel , the data 2) and 3) converted to "#name?" but data 1) is ok. I have made some research if i replace data 2) and 3) with (C) to replace with (M) and (K) ,Everthing is fine .. My question is what is (C) ? Is it a excel formula ? Looking forward for your kindly answer Thanks Ujoon Hi the problem is the minus si...

Copying from one ListControl to Another...
Hello everyone, I have been studying how List Controls work, and now I have two List Controls, A and B. Both of the list controls are in Report_View and they both have the same # of columns. What I would like to do is copy each entry of A into B. I use CListCtrl::GetItem(..) to get the item, and I use CListCtrl::InsertItem(..) to insert the item into B. However, this is not working. It is only inserting the first subitem. Is it possible to copy the entire item i.e, subitems 0-2 from CListCtrl A into CListCtrl B? Thanks ...

Outlook,can't print email addresses, because some ave only names
I would like to print my contact list, but some only have names. When I look in tools the email addresses are all there, but there is no print on the file menu. Thanks you Try to clarify your question. -- Russ Valentine [MVP-Outlook] "troubled" <troubled@discussions.microsoft.com> wrote in message news:E1953782-968C-4F6D-9DEA-69B1D7776D82@microsoft.com... >I would like to print my contact list, but some only have names. When I >look > in tools the email addresses are all there, but there is no print on the > file > menu. Thanks you troubled <trouble...

Another question about IF(COUNTIF) checks in Excel
<Tiff1618@discussions.microsoft.com> wrote in message > news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com... > > Hey again, > > > > Is there a formula I can use to figure out if there is one specific phrase > > in a selection? > > > > I'm updating the attendance prgram at the school that I work at. Every > > student has their own attendance sheet in Excel, and each sheet sort of > > looks > > like this: > > > > |Monday| > > Period 1:| A | (A=Absent; S=Seat time) > > Per...

Another If/Ten
HI all. Trying to use IF/THEN to look at Column A on one sheet, if any cell in that column equals today() would like cells from that row to fill in another sheet. Is this possible? Any ideas on how to make that happen?? Thanks. Flue. Maybe you can use format|Conditional formatting. Select the range to color (I used A2:X999). With A2 the activecell, I did: format|conditional formatting formula is: =($a2=today()) and give it a nice format Brian Bennett wrote: > > HI all. > > Trying to use IF/THEN to look at Column A on one sheet, if any cell in that > column equal...

Suggest Name Now Not Working
All of a sudden the suggest name option no longer works in Outlook 2003. Any way to repair this or fix it? If not and I have to create a new profile, what's the best way to do it and keep all my customizations, email accounts, etc. intact? Thanks! Try shutting down Outlook and renaming the .nk2 file associated with your profile. Outlook should start building a new one for you when you send more messages. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.as...

How can I open a file saved in another version of Publisher?
I have Publisher 2000 and I am trying to open a .pub file but the message says it cannot open because the file is save in a different version of Publisher. Is there a way to open the file in Publisher 2000? Any help would be greatly appreciated. YvetteR wrote: > I have Publisher 2000 and I am trying to open a .pub file but the > message says it cannot open because the file is save in a different > version of Publisher. Is there a way to open the file in Publisher > 2000? Any help would be greatly appreciated. More than likely, this file was created in Publisher 2002 or 2003. Y...

Another Gag thread
What's the difference between an Irish wedding and an Irish funeral? One less drunk at the funeral. The mayor of New Orleans was asked about his position on Roe vs. Wade. He said he didn't really care how people got out of the city. -- * "AB" <AB@discussions.microsoft.com> wrote in message news:2B46FD0D-3AA8-4D81-B90A-3FB512EE1F8F@microsoft.com... What's the difference between an Irish wedding and an Irish funeral? One less drunk at the funeral. By looking at a picture of a person, you have to decide if he is a computer geek or a serial k...

Referencing another field
Hello, I have a Combobox field called "WorkerID" on a form. This field is not tied to the Forms data source. It is pretty much floating, no control source, but it does have a Row Source Type, Select Query that pulls Worker IDs form a table called "tbl_WorkerIDs". This table has both WorkerIDs and WorkerNames. I want to add another floating combobox field to the form to display the WorkerNames. So if I change the combobox WorkerID I need the WorkerName to change automatically. How do I configure the WorkerName combobox to look at the WorkerID field and upda...

Upgrade to another another language
Is it possible to upgrade the CRM to change the language? I know there is a Dutch disk kit (T07-00218), but can I install this version over an existing English CRM. It is important that all the user data is remains unaltered. Can somebody help me. Thanks, Bernard Bernard, Installing another language version of crm over an existing implementation will not work. You will need to reinstall crm. Exporting customizations and importing again should not be possible, because of the language code in the xml file. But when you change the language codes in the xml file to the correct value the...

Pop-Up asks for Money account name
I have been running MS Money 2003 Deluxe, Version 11.0 for the past couple of years, with no problems. Just recently, when I open MS Money I get a pop-up that asks for Money account name. This pop-up header has ther words; Choose a Money Account Jusg below the header it reads; Online File Received If you need to open a different Money file, click the Postpone button and open the correct file. Type of information: QIF Statement Type of account: Unknown account type Account number: Select the Money account that this outline information should be associated with. (If you haven't yet se...

Copy e-mail to address on another domain?
How would I setup for a users email user@domain1.com to also get sent to blah@otherdomain.com? Server 2003,Exchange 2003.. The reason I ask is we run an IT company and wish to have each of our clients administrator@ accounts also send a copy of incoming e-mail to server.reporting@ourcompany.com so someone at our firm can keep track of all the administrator e-mail in a central place. Thanks! -L In news:uERgb6RqFHA.2776@TK2MSFTNGP10.phx.gbl, Locke Nash Cole <junkmauler@hotmail.com> typed: > How would I setup for a users email user@domain1.com to also get sent > to blah@o...

To be able to assign a price sheet to another price sheet
When using Extended Pricing, would like the ability to assign one price sheet to another price sheet. Here is specifically what you are trying to accomplish: 1. Have a net price on the BASEBOOK 2. Have another price sheet, say TEST that calculates 90% of net price on BASEBOOK 3. Have another price sheet, say TWO that calculates 80% off the TEST price sheet. 4. Assign TWO to a Customer. Basically the customer's price is calculated as follows: 80% of 90% of list price in BASEBOOK. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestion...