Separating First Name(s) and Last Name into Separate Columns

I have a spreadsheet with one column ("name") that contains names in the 
following formats occuring randomly down the list:

Firstname Lastname
Firstname & Firstname Lastname
Firstname Lastname & Firstname Lastname

I need to get them all into two new columns "firstname" and "lastname" and 
I'm having difficulties writing a formula to account for all 3 scenarios in 
one cell.  (If I have to do it in multiple steps - so be it!)

For the 1st example, the contents of the new "firstname" column should be 
Firstname, while the "lastname" column should be Lastname.
For the 2nd example, the contents of the new "firstname" column should be 
Firstname & Firstname, while the "lastname" column should be Lastname.
For the 3rd example, the contents of the new "firstname" column should be 
Firstname & Firstname, while the "lastname" column should be Lastname & 
Lastname.

Examples:
Joe Blow
Joe & Julie Blow
Joe Blow & Jane Schmo

"firstname" column contents should be
Joe
Joe & Julie
Joe & Jane

"lastname" column contents should be
Blow
Blow
Blow & Schmo

Thanks for helping! 


0
srpouch (23)
12/16/2008 9:47:20 PM
excel 39879 articles. 2 followers. Follow

4 Replies
610 Views

Similar Articles

[PageSpeed] 17

On Tue, 16 Dec 2008 15:47:20 -0600, "Shane R. Pouch" <srpouch@sbcglobal.net>
wrote:

>I have a spreadsheet with one column ("name") that contains names in the 
>following formats occuring randomly down the list:
>
>Firstname Lastname
>Firstname & Firstname Lastname
>Firstname Lastname & Firstname Lastname
>
>I need to get them all into two new columns "firstname" and "lastname" and 
>I'm having difficulties writing a formula to account for all 3 scenarios in 
>one cell.  (If I have to do it in multiple steps - so be it!)
>
>For the 1st example, the contents of the new "firstname" column should be 
>Firstname, while the "lastname" column should be Lastname.
>For the 2nd example, the contents of the new "firstname" column should be 
>Firstname & Firstname, while the "lastname" column should be Lastname.
>For the 3rd example, the contents of the new "firstname" column should be 
>Firstname & Firstname, while the "lastname" column should be Lastname & 
>Lastname.
>
>Examples:
>Joe Blow
>Joe & Julie Blow
>Joe Blow & Jane Schmo
>
>"firstname" column contents should be
>Joe
>Joe & Julie
>Joe & Jane
>
>"lastname" column contents should be
>Blow
>Blow
>Blow & Schmo
>
>Thanks for helping! 
>

Given your rather specific data layouts, the following VBA Sub will parse the
names as you describe.

To enter the sub, <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.

The code operates on any range you have selected, and puts the FirstName and
LastNames into the adjacent columns, clearing those columns first.

Note that if your names do not precisely match one of the patterns you have
provided, nothing will happen.

==============================================
Option Explicit
Sub PrsNm()
Dim c As Range
Dim aFn() As String, aLn() As String
Dim re As Object, mc As Object, m As Object
Dim i As Long, j As Long, k As Long
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(\w+)\s+(\w+)$|^(\w+)\s+&\s+" & _
    "(\w+)\s+(\w+)$|^(\w+)\s+(\w+)\s+&\s+(\w+)\s+(\w+)$"

For Each c In Selection
j = 0: k = 0
ReDim aFn(j): ReDim aLn(k)
Range(c.Offset(0, 1), c.Offset(0, 2)).ClearContents
    If re.test(c.Value) Then
        Set mc = re.Execute(c.Value)
            For i = 1 To 9
                Select Case i
                    Case Is = 1, 3, 4, 6, 8
                If Not IsEmpty(mc(0).submatches(i - 1)) Then
                    ReDim Preserve aFn(j)
                        aFn(j) = mc(0).submatches(i - 1)
                    j = j + 1
                End If
                    Case Is = 2, 5, 7, 9
                If Not IsEmpty(mc(0).submatches(i - 1)) Then
                    ReDim Preserve aLn(k)
                        aLn(k) = mc(0).submatches(i - 1)
                    k = k + 1
                End If
                End Select
            Next i
    End If
c.Offset(0, 1).Value = Join(aFn, " & ")
c.Offset(0, 2).Value = Join(aLn, " & ")
Next c
End Sub
===================================
--ron
0
ronrosenfeld (3122)
12/17/2008 1:03:45 AM
Ron,
Thanks!  Works great!  Now I just have to figure out HOW it works.

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:qfjgk496ljb5pvp4u2la74hcu5qie30aqf@4ax.com...
> On Tue, 16 Dec 2008 15:47:20 -0600, "Shane R. Pouch" 
> <srpouch@sbcglobal.net>
> wrote:
>
>>I have a spreadsheet with one column ("name") that contains names in the
>>following formats occuring randomly down the list:
>>
>>Firstname Lastname
>>Firstname & Firstname Lastname
>>Firstname Lastname & Firstname Lastname
>>
>>I need to get them all into two new columns "firstname" and "lastname" and
>>I'm having difficulties writing a formula to account for all 3 scenarios 
>>in
>>one cell.  (If I have to do it in multiple steps - so be it!)
>>
>>For the 1st example, the contents of the new "firstname" column should be
>>Firstname, while the "lastname" column should be Lastname.
>>For the 2nd example, the contents of the new "firstname" column should be
>>Firstname & Firstname, while the "lastname" column should be Lastname.
>>For the 3rd example, the contents of the new "firstname" column should be
>>Firstname & Firstname, while the "lastname" column should be Lastname &
>>Lastname.
>>
>>Examples:
>>Joe Blow
>>Joe & Julie Blow
>>Joe Blow & Jane Schmo
>>
>>"firstname" column contents should be
>>Joe
>>Joe & Julie
>>Joe & Jane
>>
>>"lastname" column contents should be
>>Blow
>>Blow
>>Blow & Schmo
>>
>>Thanks for helping!
>>
>
> Given your rather specific data layouts, the following VBA Sub will parse 
> the
> names as you describe.
>
> To enter the sub, <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.
>
> The code operates on any range you have selected, and puts the FirstName 
> and
> LastNames into the adjacent columns, clearing those columns first.
>
> Note that if your names do not precisely match one of the patterns you 
> have
> provided, nothing will happen.
>
> ==============================================
> Option Explicit
> Sub PrsNm()
> Dim c As Range
> Dim aFn() As String, aLn() As String
> Dim re As Object, mc As Object, m As Object
> Dim i As Long, j As Long, k As Long
> Set re = CreateObject("vbscript.regexp")
> re.Pattern = "^(\w+)\s+(\w+)$|^(\w+)\s+&\s+" & _
>    "(\w+)\s+(\w+)$|^(\w+)\s+(\w+)\s+&\s+(\w+)\s+(\w+)$"
>
> For Each c In Selection
> j = 0: k = 0
> ReDim aFn(j): ReDim aLn(k)
> Range(c.Offset(0, 1), c.Offset(0, 2)).ClearContents
>    If re.test(c.Value) Then
>        Set mc = re.Execute(c.Value)
>            For i = 1 To 9
>                Select Case i
>                    Case Is = 1, 3, 4, 6, 8
>                If Not IsEmpty(mc(0).submatches(i - 1)) Then
>                    ReDim Preserve aFn(j)
>                        aFn(j) = mc(0).submatches(i - 1)
>                    j = j + 1
>                End If
>                    Case Is = 2, 5, 7, 9
>                If Not IsEmpty(mc(0).submatches(i - 1)) Then
>                    ReDim Preserve aLn(k)
>                        aLn(k) = mc(0).submatches(i - 1)
>                    k = k + 1
>                End If
>                End Select
>            Next i
>    End If
> c.Offset(0, 1).Value = Join(aFn, " & ")
> c.Offset(0, 2).Value = Join(aLn, " & ")
> Next c
> End Sub
> ===================================
> --ron 


0
srpouch (23)
12/17/2008 3:52:21 AM
On Tue, 16 Dec 2008 21:52:21 -0600, "Shane R. Pouch" <srpouch@sbcglobal.net>
wrote:

>Ron,
>Thanks!  Works great!  Now I just have to figure out HOW it works.

You're welcome.  Glad to help.

It uses Regular Expressions to parse the segments.  (The parsing could be done
using plain VBA functions, but for me it is simpler, and faster, to use
regexes).

Here are some online references:

Regular Expressions
http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://msdn2.microsoft.com/en-us/library/ms974619.aspx
http://www.regex-guru.info/
--ron
0
ronrosenfeld (3122)
12/17/2008 11:29:20 AM
Very cool, Ron.  I'm learnin' - Thanks a million!!

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:mhohk4tksknelei7jd6s95ns4om5hvotc3@4ax.com...
> On Tue, 16 Dec 2008 21:52:21 -0600, "Shane R. Pouch" 
> <srpouch@sbcglobal.net>
> wrote:
>
>>Ron,
>>Thanks!  Works great!  Now I just have to figure out HOW it works.
>
> You're welcome.  Glad to help.
>
> It uses Regular Expressions to parse the segments.  (The parsing could be 
> done
> using plain VBA functions, but for me it is simpler, and faster, to use
> regexes).
>
> Here are some online references:
>
> Regular Expressions
> http://www.regular-expressions.info/reference.html
> http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb
> http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
> http://msdn2.microsoft.com/en-us/library/ms974619.aspx
> http://www.regex-guru.info/
> --ron 


0
srpouch (23)
12/25/2008 3:41:38 AM
Reply:

Similar Artilces:

Excel VBA
Hi, I'm using a named range to mark the right most and bottom most column and rows of interest. "lastrow" and "lastcolumn" respectively. There i data outside this area that I wish to keep but the number o rows/columns inside can grow with the project. My intention is to select this area with a construct similar to cells("lastrow","lastcolumn").select Of course this will not work unless I can derive the column index fro "lastcolumn" and row index from "lastrow". How is this achieved? I've just thought I can place a name...

Site Name
i am installing great plain 8.0 and i am getting a error registration key is no valid what will be the site name. The site name is the Registered Company Name that was given to you along with your registration keys - it has to appear exactly the same, or else you will get an error. -- Victoria Yudin GP MVP "gp" <gp@discussions.microsoft.com> wrote in message news:6A6D18F7-BE25-4009-A0C2-5F0FD58755A9@microsoft.com... >i am installing great plain 8.0 and i am getting a error registration key >is > no valid what will be the site name. > > ...

Delete row with empty cell in Column A?
I have a .CSV file exported from our parts control application that needs a bit of tweeking to make it presentable. I would like to delete any row that has an empty cell in Column A. How can I do this? Does it require a macro? STeveK Doesn't require a macro. Select column A. Choose Edit/Go To.../Special. Select Blanks. Choose Edit/Delete... and choose Entire Row from the dialog that opens. In article <epn13akPGHA.648@TK2MSFTNGP14.phx.gbl>, "SteveK" <sk-reeemovethis@dolby-reeemovethis.com> wrote: > I have a .CSV file exported from our parts control ap...

Dynamicly change spreadsheet tab names depending on cell value
Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 for each day of the month) These tabs are named - 1,2,3 e.t.c Is it possible to define rule that would ad "!" to day which is weekend day. In other words is it possible to dynamicly change tab name depending from value of cell ? If this is possbile how do i do this ? Right-click on the spreadsheet tab, select View Code and paste this in:- Private Sub Worksheet_Activate() ActiveSheet.Name = Range("A1").Value End Sub To test this I put the date in cell B2 and this formula into A1:- =TEX...

Outlook 2003 Name Resolution Eroor W2K Server Exchange 2000
Hey I have seen this question popping up alot in google groups however I have not found a solution yet. After upgrading to office 2k3 I am unable to connect to the exchange server using check name. It returns the error: "The name could not be resolved. The connection to the Microsoft Exhange Server is unavailable. Outlook must be connected to complete this action." If I uninstall reinstall Outlook XP I am able to connect no problem so long as I delete the old profile. I can even have have both installed at the same time and XP will work while 2k3 will not. I also tried sett...

It's HOT, it's NEW and it PAYS !!
Dear Affiliate, Just released ! Get in on the ground floor of this new inexpensive program. Amazing new program PAYS $20 INSTANTLY to you and has built-in residual income. There IS NO OTHER program like it!! And I mean ENDLESS CHAINS OF RESIDUAL INCOME! $29 one time purchase, brings a never ending income. No matrix to fill, No mlm, No waiting to get paid... This program will make more money for you faster than anything you have ever seen, and it only costs $29.00! Join today, get paid today! You are paid your $20 commission up front! No middle man, no waiting! The second someone ...

descriminates what O/S
Is there any ready code that discriminates ( possible by user message) what O/S the user has? For instance Windows 2000 or Windows XP Thanks Galia >Is there any ready code that discriminates ( possible by user message) what >O/S the user has? >For instance Windows 2000 or Windows XP Galia, Are you perhaps looking for the GetVersionEx API? Dave ...

Removing Public Folder Replica(s)
I will be removing an Exchange 5.5 site from our environment later this week. I have found an article that goes over the process. One of the steps is to remove Public Folder Replicas from Exchange_Site_B. This is where I'm having trouble. When I try and remove the replica from Site_B, I get an error that I don't have permissions to perform this operation on the Information Store. The exact message follows: You do not have the permission required to complete the operation on the information store. Microsoft Exchange Information Store ID no: c1040af3 Now, I have checked permissio...

Combine Workbooks as separate worksheets
I lose column widths and row heights when I copy unto a new worksheet. Does anyone have any suggestions. I have four separate workbooks that need to be combined into one workbook but on a separate worksheet. I am using Microsoft Office XP, Excel 2002. Thanks, SAL Hi Sal You can use copy pastespecial (column width) line for the columns But there is no option for rows. If all the rows have the same row height then it is easy to do also. See also http://www.rondebruin.nl/copy3.htm If you need help with the pastespecial code line post back -- Regards Ron de Bruin http://www.rondebru...

address separated by a diamond shape object by cty and state zip
927 spencer road (*) Honolulu, HI 96833 (*) 888-000-8787 (8) needs to be diamond shape objects ; looks like a bullet HOW DO I FORMAT THAT IN A SINGLE LINE? PowerPoint 2007 I have just put up a file for you at:- http://www.pierrefondes.com/ Item number 99. If my comments have helped please hit Yes. Thanks. "MSLORNA808" wrote: > 927 spencer road (*) Honolulu, HI 96833 (*) 888-000-8787 > > (8) needs to be diamond shape objects ; looks like a bullet > > HOW DO I FORMAT THAT IN A SINGLE LINE? ...

Display last "Heading 1" in footer
Hi, I have 45 page document with a contents page using Heading 1 and Heading 2. The Heading 1 are my major titles and I would like them to also display in the footer of every page. Is there a way to do this? For example, the first Heading 1 is Contents so all pages (including the one with the Contents title in it) should have Contents in the footer. The next one is Communications & Performing Arts, so that page and all following pages should stop displaying Contents and should start displaying Communications & Performing Arts instead until we get to the next Heading 1 and...

one last error, exchange 2007
once ive cleared this one the event viewer will be devoid of red exes! Event Type: Error Event Source: MSExchangeFBPublish Event Category: General Event ID: 8207 Date: 28/02/2007 Time: 12:48:50 User: N/A Computer: XXXXXX Description: Error updating public folder with free/busy information on virtual machine XXXXXXX. The error number is 0x80004005. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. any solutions for a fix on 2007 ? On Wed, 28 Feb 2007 13:35:35 -0000, "luna" <luna_s@themoon.com> wrote: >once ive cleare...

auto name caching
> I move from using Outlook Express to Outlook 2003. > > In Express, when I type the first few letters of a recipients name in the > To... field, the whole e-mail address will show up. THe problem is this does > not happen in Outlook 2003. What do I have to do to have the recipients names > automatically come up just by typing the first few letters of there name, > like in express. Please advise. Thanks! That should happen in OL2003 also as long as the "Suggest names...." box is checked here: Tools menu > Options > Email > Advanced. "Rob D."...

Display Name of each Data Series
I have an X-Y chart with, say, 3 data series, named 2008, 2009 and 2010. I want to get rid of the legend (by deleting it) and instead have the above names displayed on the relevant curve. Is there a way in Excel XP (aided by VBA) to display the name of each data series on the series itself? If one tries: Format data series -> Data Labels -> Series Name, the Series Name is displayed 10 times if there are 10 data points! I want it displayed only once, somewhere in the middle of the curve representing that data series. Hi, Either, select a single point in the series an...

Upgraded to Excel XP, Excel 97 Vlookup's corrupted
We would like to upgrade to Excel 2002 or(XP) however we have a huge amount of excel 97 files that are loaded with Vlookup formulas similar to =VLOOKUP('F:\Prices-Dec 2004-Master Program\Master Pricing - Pacesetter.xls'!Joist_Package_2,'F:\Prices-Dec 2004-Master Program\Master Pricing - Pacesetter.xls'!Prices_To_Change,MATCH('F:\Prices-Dec 2004-Master Program\Master Pricing - Pacesetter.xls'!Apaloosa_A_IX_Series,'F:\Prices-Dec 2004-Master Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE) When we load on Excel 2002 all our Vlookup results ha...

View others BCC's receivers
Hi every one, When you receive an email, there is any way to see if that email has been sent to other people using BCC? I mean, it is possible to see the BCC's receivers? Regards Briega ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ On Fri, 2 Jan 2004 06:50:12 -0600, briega <briega.zfz1x@outlookforum.com> wrote: >When you receive an email, there is any way to see if that email has >been sent to other people using BCC? I mean, it is possible to see ...

Formula won't copy down the column
I am trying to copy a formula down a column. I have done it for years and worked fine. This time, the first result appears in each cell, but if you look at the formula in copied cells, they show the correct formula has changed as I dragged it down the column. So, the formula changes as I drag, but the answer stays the same as the original cell. The data was sent to me by someone else and I am working with it. I have even copied just the delivered data to a new spreadsheet and the same result. Please help It sounds like Calculation in Excel is set to Manual. Try this: From the Ex...

Inserting separate A5 page in Catologue
Hello all, I'm busy with a catologue for our in-house training. It will be A4 folded (A5 size). I also want to put an application in the centre of the booklet, which will only be a one-sided A5 page. I know I can print this seperately, but I am just wondering if it is possible to insert a 'loose' A5 page in the centre of my document instead. That will make it much easier instead of having to update the two different files. (hope that makes sense) Any help will be greatly appreciated. Thanks in advance Aitch If you convert your catalog merge to a publication in step 5 of th...

Any disk utilities to correct overly long file names/folder paths?
Hello, A company I work with has many folders nested within subfolders, within subfolders with really long folder names that when you go all the way to the end exceed 255 characters. Robocopy, richtools, etc all fail when copying these really long folder paths and file names. Is there any utility I can run to correct these or at least identify all of them? I need to copy a 4TB volume and it keeps failing. I can't have the server offline for too long and I can't wait for it to fail to fix all of them. Thanks Hello boe, Never had problems when using robocop...

SELECT FROM Query name
I am using a SELECT statement as a rowsource on a form listbox. I have a query named 01-Accounts Query and the SELECT stmt reads as follows... SELECT 01-Accounts_Query.Name, 01-Accounts_Query.Date FROM 01-Accounts_Query I get a syntax error in query message. How do I fix this? gator wrote: >I am using a SELECT statement as a rowsource on a form listbox. I have a >query named 01-Accounts Query and the SELECT stmt reads as follows... > >SELECT 01-Accounts_Query.Name, 01-Accounts_Query.Date FROM 01-Accounts_Query > >I get a syntax error in query message...

Vendor name in Requisition Management module
I would like the vendor name to appear in the Requisition Management module but was told by Microsoft Support that it was not possible to customize this product. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsG...

naming references in workbooks in Excel X
Having difficulty in applying Book-Level and Sheet-Level Names in Workbooks. When I define a name in the Define Name dialog box and I then select the name in the Name Box, it will select the reference on the worksheet that I have used to define the name. If I then move to another worksheet in the same workbook with identical layout, the name I have defined appears in the Name Box, but when I select it, it returns to select the reference in the Worksheet where I made the original definition. This does not always happen but I cannot find a sure way of defining names in one worksheet that will al...

Please help with code to open pdf's and modify using access database
I am looking for some visual basic to take a list of file names (pdf's) in an excell spreadsheet or access query output and use that list to cycle through a folder on a network drive containing the referenced pdf files. If the filename in the output equals the filename on the server, then it would open the file from the server, paste a text box (in pdf format) in the upper right hand corner, save the file as the same file name in the same folder on the server and close the file then move on to the next and do the same. I have adobe acrobat professional version 8. This would have to be d...

Drop down bar to hide column according to the content of a cell
Hi all, I need help. How can i write a macro such that when the user select (e.g. 2 ) from the dropdown for, it hides column G:Z when the user select (e.g. 5) from the dropdown form, it hides Column J:Z Thank you so much. Cheers, Daphie I dropped a combobox from the control toolbox toolbar onto a worksheet. I assigned a range with all the values I wanted (0-10 for my test) to the listfillrange. Then I doubleclicked on that combobox (while in design mode) and used this code: Option Explicit Private Sub ComboBox1_Change() Dim HowManyCols As Long HowManyCols = Me.ComboBox1...

User Name and Password #3
Have Outllook 2000. Attempting to add second email name to outlook. Getting error message when trying to send/receive new emails. Content of error message is server will not accept user name and password. Dialog box requesting user name and password appears I fill in information but continue to get error. Define "second email name" - there is no such thing. Are you trying to = add an account? A user profile? And please post the exact error message = you get from your ISP as the paraphrasing does nothing to assist with = troubleshooting. --=20 Milly Staples [MVP - Outlook] ...