split one column to multiple columns

I have data formatted in column A like:

Sheldon Barnes <nhr4man@nc.rr.com>, 
Miriam DURVAMC Mil34ler <miriam....miller@va.gov>, 
Eddie Barnes <e.barne26s@verizon.net>, 
Deloris Bell-Johnson <db12345ell67@nc.rr.com>, 
Brenda Bethea <bbethea@email.un453c.edu>, 
jessie bowen <jbowen8kdkdkd871@aol.com>, 

How can i separate  into three columns?
First Name, Last Name, E-mail

Many Thanks in Advance
Greg
0
Utf
12/13/2009 8:30:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
1673 Views

Similar Articles

[PageSpeed] 33

Hi Greg,

See if Text To Column under Data will do that for you.  Use Space as the 
Delimiter.

It worked for me on a simple test of your data but did leave the "<" in 
front of the e-mail address.  Perhaps a remove > replace "< " replace with 
nothing > Ok will take care of that.

Also, the last name is left underlined and in blue font color, a couple of 
steps to clear that up will be in order.  Select and remove the underline 
and while selected return font color to Automatic.

HTH
Regards,
Howard

"Greg" <Greg@discussions.microsoft.com> wrote in message 
news:378F6002-B7E4-4A3F-8A40-454EE0336C94@microsoft.com...
>I have data formatted in column A like:
>
> Sheldon Barnes <nhr4man@nc.rr.com>,
> Miriam DURVAMC Mil34ler <miriam....miller@va.gov>,
> Eddie Barnes <e.barne26s@verizon.net>,
> Deloris Bell-Johnson <db12345ell67@nc.rr.com>,
> Brenda Bethea <bbethea@email.un453c.edu>,
> jessie bowen <jbowen8kdkdkd871@aol.com>,
>
> How can i separate  into three columns?
> First Name, Last Name, E-mail
>
> Many Thanks in Advance
> Greg 


0
L
12/13/2009 9:46:39 PM
What did that method do with middle names, as in the second entry in the 
OP's list of examples?
--
David Biddulph

"L. Howard Kittle" <lhkittle@comcast.net> wrote in message 
news:%23sNYC3DfKHA.1652@TK2MSFTNGP05.phx.gbl...
> Hi Greg,
>
> See if Text To Column under Data will do that for you.  Use Space as the 
> Delimiter.
>
> It worked for me on a simple test of your data but did leave the "<" in 
> front of the e-mail address.  Perhaps a remove > replace "< " replace with 
> nothing > Ok will take care of that.
>
> Also, the last name is left underlined and in blue font color, a couple of 
> steps to clear that up will be in order.  Select and remove the underline 
> and while selected return font color to Automatic.
>
> HTH
> Regards,
> Howard
>
> "Greg" <Greg@discussions.microsoft.com> wrote in message 
> news:378F6002-B7E4-4A3F-8A40-454EE0336C94@microsoft.com...
>>I have data formatted in column A like:
>>
>> Sheldon Barnes <nhr4man@nc.rr.com>,
>> Miriam DURVAMC Mil34ler <miriam....miller@va.gov>,
>> Eddie Barnes <e.barne26s@verizon.net>,
>> Deloris Bell-Johnson <db12345ell67@nc.rr.com>,
>> Brenda Bethea <bbethea@email.un453c.edu>,
>> jessie bowen <jbowen8kdkdkd871@aol.com>,
>>
>> How can i separate  into three columns?
>> First Name, Last Name, E-mail
>>
>> Many Thanks in Advance
>> Greg
>
> 


0
David
12/14/2009 8:29:16 AM
On Sun, 13 Dec 2009 12:30:01 -0800, Greg <Greg@discussions.microsoft.com>
wrote:

>I have data formatted in column A like:
>
>Sheldon Barnes <nhr4man@nc.rr.com>, 
>Miriam DURVAMC Mil34ler <miriam....miller@va.gov>, 
>Eddie Barnes <e.barne26s@verizon.net>, 
>Deloris Bell-Johnson <db12345ell67@nc.rr.com>, 
>Brenda Bethea <bbethea@email.un453c.edu>, 
>jessie bowen <jbowen8kdkdkd871@aol.com>, 
>
>How can i separate  into three columns?
>First Name, Last Name, E-mail
>
>Many Thanks in Advance
>Greg

These formulas rely on the following:

1.  First Name is first word
2.  Last Name is the last word before the "<"
3.  Email is at the end and enclosed  "<...>"

With data in A2:

B2 (FN):	=LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)

C2 (LN):
=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,FIND("<",A2)-1))," ",REPT(" ",99)),99))

D2 (EMAIL):
=MID(TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99)),2,
FIND(">",TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99)))-2)

Or you could use 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 of cells to be parsed.
Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

==================================================
Option Explicit
Sub parseFNLNEmail()
 Dim re As Object, mc As Object
 Dim rg As Range, c As Range
 Dim i As Long, s As String

Set re = CreateObject("vbscript.regexp")
    re.Global = True
    re.Pattern = "(\w+).*\s(\S+)\s.*<([^>]*)"
    
Set rg = Selection  'or set up however is most efficient
For Each c In rg
    Range(c.Offset(0, 1), c.Offset(0, 3)).ClearContents
    s = c.Value
    If re.test(s) = True Then
        Set mc = re.Execute(s)
            For i = 1 To 3
                c.Offset(0, i).Value = mc(0).submatches(i - 1)
            Next i
    End If
Next c
End Sub
========================================================
--ron
0
Ron
12/14/2009 12:42:45 PM
Thanks, the text to column worked great!. i thought i had used that function 
before but couldn't remember where it was at.

Thanks Again.
Greg

"L. Howard Kittle" wrote:

> Hi Greg,
> 
> See if Text To Column under Data will do that for you.  Use Space as the 
> Delimiter.
> 
> It worked for me on a simple test of your data but did leave the "<" in 
> front of the e-mail address.  Perhaps a remove > replace "< " replace with 
> nothing > Ok will take care of that.
> 
> Also, the last name is left underlined and in blue font color, a couple of 
> steps to clear that up will be in order.  Select and remove the underline 
> and while selected return font color to Automatic.
> 
> HTH
> Regards,
> Howard
> 
> "Greg" <Greg@discussions.microsoft.com> wrote in message 
> news:378F6002-B7E4-4A3F-8A40-454EE0336C94@microsoft.com...
> >I have data formatted in column A like:
> >
> > Sheldon Barnes <nhr4man@nc.rr.com>,
> > Miriam DURVAMC Mil34ler <miriam....miller@va.gov>,
> > Eddie Barnes <e.barne26s@verizon.net>,
> > Deloris Bell-Johnson <db12345ell67@nc.rr.com>,
> > Brenda Bethea <bbethea@email.un453c.edu>,
> > jessie bowen <jbowen8kdkdkd871@aol.com>,
> >
> > How can i separate  into three columns?
> > First Name, Last Name, E-mail
> >
> > Many Thanks in Advance
> > Greg 
> 
> 
> .
> 
0
Utf
12/14/2009 2:54:01 PM
I missed that one, and as you would expect it went to four columns on that 
entry.

Perhaps a cleaner way would have been use "<" as the delimiter for one try 
and then use space on the next try (need to move some data about to make 
room for the second split.  Then use =LEFT(F9,LEN(F9)-2) on the address to 
clean up the >, at the end.

Regards,
Howard

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message 
news:GPadnR51-b39arjWnZ2dnUVZ8lKdnZ2d@bt.com...
> What did that method do with middle names, as in the second entry in the 
> OP's list of examples?
> --
> David Biddulph
>
> "L. Howard Kittle" <lhkittle@comcast.net> wrote in message 
> news:%23sNYC3DfKHA.1652@TK2MSFTNGP05.phx.gbl...
>> Hi Greg,
>>
>> See if Text To Column under Data will do that for you.  Use Space as the 
>> Delimiter.
>>
>> It worked for me on a simple test of your data but did leave the "<" in 
>> front of the e-mail address.  Perhaps a remove > replace "< " replace 
>> with nothing > Ok will take care of that.
>>
>> Also, the last name is left underlined and in blue font color, a couple 
>> of steps to clear that up will be in order.  Select and remove the 
>> underline and while selected return font color to Automatic.
>>
>> HTH
>> Regards,
>> Howard
>>
>> "Greg" <Greg@discussions.microsoft.com> wrote in message 
>> news:378F6002-B7E4-4A3F-8A40-454EE0336C94@microsoft.com...
>>>I have data formatted in column A like:
>>>
>>> Sheldon Barnes <nhr4man@nc.rr.com>,
>>> Miriam DURVAMC Mil34ler <miriam....miller@va.gov>,
>>> Eddie Barnes <e.barne26s@verizon.net>,
>>> Deloris Bell-Johnson <db12345ell67@nc.rr.com>,
>>> Brenda Bethea <bbethea@email.un453c.edu>,
>>> jessie bowen <jbowen8kdkdkd871@aol.com>,
>>>
>>> How can i separate  into three columns?
>>> First Name, Last Name, E-mail
>>>
>>> Many Thanks in Advance
>>> Greg
>>
>>
>
> 


0
L
12/14/2009 8:13:33 PM
Reply:

Similar Artilces:

multiple records from one
I have a table that has an activity with a related table with start dates and end dates for a particular category of activity such as planning phase, construction phase, operating phase, completed, etc. I would like to create a query for a report that will return the year of the activity to group on the report by year, but the kicker is if the date range of multiple records spans multiple years, I need a record for each date that the activity is occuring. I know that I can use the min and max function when grouping the related records together to get a single record with a sing...

Multiple outlook.exe's running in the processes tab of the Task Manager
I've noticed a regular occurrence when shutting my machine down for the night. I'm a long time user of windows so I still shut down all my applications before I shut down the operating system. Call me old but I believe it keeps the system clean. But I've noticed a problem. OS: XP Professional Mail App: Outlook 2002 After shutting down all applications, I attempt to shut down the machine and I get a window indicating OUTLOOK.EXE is still running and it asks me to shut it down, so I do...Then the same window pops up again, so I end the process again. Well, what I have noti...

Split tasks a splitting headache
Inherited another Project IMS. I am noticing right off that the previous scheduler has been starting tasks without their FS preds completing. Yeah, it does show up as a spit bar in the gantt chart. But, does anyone know a way to filter out just task that started before predecessor completion? I'm dealing with several thousand lines. Thanks. -- trailerpup ------------------------------------------------------------------------ trailerpup's Profile: http://forums.techarena.in/members/116596.htm View this thread: http://forums.techarena.in/microsoft-project/1290853.htm ...

How to randomly split a whole dataset into two sub-dataset?
Hi, At your possible convenience, might anyone please kindly answer my question? Thank you very much. How to "RANDOMLY" split the whole data set (n=2000) into two sub dataset (n=1000; n=1000) in SPSS or Excel? Thank you very much. Please take care Caroline zencaroline <zencaroline@gmail.com> writes: > How to "RANDOMLY" split the whole data set (n=2000) into two sub > dataset (n=1000; n=1000) in SPSS or Excel? Create a new variable whose value is randomly distributed. Sort the data on this variable. Take the first 1000 cases ...

Multiply entire column...
I haven't worked with Excel for a number of years... I have an Excel price list...all prices are reflecting my COST... If there not a way to tell the entire column to display as "content o cell x 2.5"? I know that I can manually enter it into each cell ( A1*2.5 ) ( A2*2. ) etc...but is there not a way to get it to do the entire column?? Thanks in advance -- Message posted from http://www.ExcelForum.com Ajx22, enter 2.5 in a cell somewhere and copy it. Then select all the cells in Column A (you can click the column heading) and Edit > Paste Special > Multiply > OK....

One email cannot be deleted
Has anyone ever seen it when one email is there, but you can't open it, move it, delete it, reply to it? Any thoughts on how to get rid of it? Windows 2000 with Office 2000. Thanks! Eric ...

Conditional Formatting --- copying to multiple rows
I have a small worksheet, columns A-F are information about an item. Column G will have a value of "Yes" or "No" (or "TRUE" or "FALSE"). If Column G, row 2 is "TRUE" then the format of the font in that cell should be a Bold Red, and the font in the cells A2-F2 should also turn red. If "FALSE" then the text remains black. I can Copy and Paste Special the Format, but then rows 3-2500 are still tied to G2. I can't seem to get the conditional formatting to automatically change the the correspond row/column G. Does that m...

Unwanted replicated split screen
I know I could re-install excell but before I do, is there anyone here who can help me disable a setting that I have no idea how it came about. I can only describe it as 2 identical execl grids on the same worksheet. Accompanying image will show you. Help. Thanks. +-------------------------------------------------------------------+ |Filename: excel_display_duplication.GIF | |Download: http://www.excelforum.com/attachment.php?postid=3855 | +-------------------------------------------------------------------+ -- BoyLeroy -----------------------------------...

Column Headings
Firstly, Happy New Year to you all. In my Outlook 2003 IN BOX I have lost the FROM column. Can anybody tell me how to get it back? Cheers Greg Hi, Greg; Right-click any of the remaining column headers, select "field chooser," find the "from" field, and drag it to the row of column headers. And a Happy New Year to you, IanRoy "Greg" wrote: > Firstly, Happy New Year to you all. > > In my Outlook 2003 IN BOX I have lost the FROM column. > > Can anybody tell me how to get it back? > > Cheers > Greg > > > ...

Cannot insert Column
When I try to insert a column, the option is grayed out - it won't let me. Is this an issue with security that I may have inadvertantly changed? I made no conscious effort to do this, and can't fix. The worksheet is my own. thank.s Do you have your worksheet protected? "Buss" wrote: > When I try to insert a column, the option is grayed out - it won't let me. > > Is this an issue with security that I may have inadvertantly changed? I > made no conscious effort to do this, and can't fix. The worksheet is my own. > > thank.s ...

unique with 2 columns
I have 2 columns with duplicates in the first column and no duplicates in the second. I neeed to only see one value for each in the left and only one of the accounts from the right. ie: abc 1234 abc 12345 bcd 251 eft 600 eft 607 I would like to see either of the abc with the 1234 or 12345 I would like to see the bcd since it's individual with the 251 I would like to see either of the eft with the 600 or the 607 Use a totals query and one of the aggregate functions (First, Last, Min, or Max) on the...

Can't find my Front End Folder after database split
I just split my database base to allow for ease of use for multiple users. Ererything, it seemed, went smoothly until i try to find the fron-end folder. It is nowhere to be found. Should it be label ****_fe.mbd like the back end? Please help. Thaanks. It will be called that, unless you typed something else in the save as dialog. I thought it ended up in the same folder as the backend after splitting. Easiest way is to go back to the original and split it again, this time take note of where you save it before you press the button. Jeanette Cunningham "Joe" <Joe@discussi...

Importing Bank Activity posts all Trans to one vendor
Everytime I import my checking account info all my transactions appear as Wal-Mart transactions although the Memo field is correctly listing the correct vendor. Anyway to correct this or must a manually change all my transactions, which almost defeats the purpose of importing my banking transactions. Thanks. In microsoft.public.money, Fran wrote: >Everytime I import my checking account info all my >transactions appear as Wal-Mart transactions although the >Memo field is correctly listing the correct vendor. > >Anyway to correct this or must a manually change all my &g...

sum column with 2 if criteria
=SUMPRODUCT(--('Working Copy'!A3:A1038="WCenterA"),--('Working Copy'!J3:J1038="1"),'Working Copy'!K3:K1038) this is the formula I entered as an array and I get an N/A#. I'm trying to look match 2 criteria and then add the remaining cells in column K. Instead of "WCenterA" I'd like to use a cell reference. I figured a combination of If and SumIF formulas, but couldn't get that to work either! Help!!! Barbara SUMPRODUCT is not an array function. It sounds like you have an #N/A in of 'Working Copy'!K3:K1038. &...

multiple ranges on Vlookup
I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. Hi lpj Not quite clear what you want to achieve here? Why not have everything in the same range? Am I correct in guessing that, if you do not find a matching record in the primary range, you then want to do a lookup in the secondary range? If so, you will have to use an...

Display Data On Split Form
Not sure how to ask this but here goes. In AC 2007 I have a split form that I want to display a value that will show regardless of the row I select in the datasheet. This is the number of vacation days one will have through-out the year and of course it changes as vacations days get used up. Any help will be appreciated, James "JamesJ" <jjy@darwin_roadrunner.com> wrote in message news:6A4DFFD9-3302-4A10-B51A-153373D266FD@microsoft.com... > Not sure how to ask this but here goes. > In AC 2007 I have a split form that I want to display a value > that will show regardl...

one or more active x control could not be displayed
On my brand new computer, I installed Office 2003 and when trying to do an email where I am copying word file, I get an error message (like I did on my old ancient computer) One of more active x controls could not be displayed becasue either your security settings prohibit running active X controls on this page or you have blocked a publisher of one of the controls. Please Help! Could you tell us what exactly you mean by "trying to do an email where I am copying word file"? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart...

Format Column by text in first row
I have multiple documents with similar data, but not always in the same format (thanks to too many users touching them). Each document has multiple sheets and I would like to write a Macro that will format a particular column to a certain Date Format. I know how to write this macro if you already know the Column, but it isn't always the same. This particular column has a header in the first cell, with the data below it; but for different sheets, it isn't in the same position. For the first sheet it could be column G and for another it could be column L. But for all the sheets, the ...

One Record Per Page Printing-Access 2007
Report for daily appointments for senior transportation drivers. Report is grouped by Driver. Keep together is selected. Detail band: Keep together-Yes; Force New Page-Before Section; Can Grow-Yes; Can Shrink-Yes Goal: Report generated so each driver has his/her own appointments. Orientation: Landscape, paper size 8.5 x 14, column width: 13.5729"; column height: 0.3069" When previewing in Print Preview one record per page is appearing rather than all appointments for a driver for a given date. I have the bands as narrow as I can make them. I am missing somethi...

How can I separate items from one cell into two?
-- bootsy here's two ideas 1) use data / text to columns 2) use a formula such as =LEFT(A1,3) or =RIGHT(A1,4) if you'ld like more assistance please post back with additional details and type out two or three examples of your data. Cheers JulieD "Bootsy" <Bootsy@discussions.microsoft.com> wrote in message news:14CF8AC7-AF0E-47E3-8B04-E3C95A9ADEF5@microsoft.com... > > -- > bootsy Peo Sjoblom wrote: <NOTHING>! http://dts-l.org/goodpost.htm http://www.netmeister.org/news/learn2quote.html -- Interim Systems and Management Accounting Gordo...

Split data into new sheets
I have a (very) long list, sorted by account code. I would like to write a macro that splits the list into separate sheets in the workbook, with a separate sheet for each account code. Ideally, I would also like to rename each sheet to show which account code the sheet contains. I have no idea, though, where to start. Any ideas? Thanks in advance. Hi bernard How many different accounts are in the list (more or less than 250)??? below some code that I use to split files by account numbers where the user has to select a cell within the column that contains the account number... Hope thi...

split column
I have a column of data in the following format column A John B. Smith What I want to do is split this column into three separate column so that it will appear as below Column B Column C Column D John B. Smith. Is there a formula that can help me do this. TIA Tlee, If all the names follow the same format (you don't say), you can use Data - Text to columns, using a space as the separator. Make sure that the columns to the right are empty before you start. -- Earl Kiosterud mvpearl omitthisword at verizon period net ---------------------------...

Change column name??????
:rolleyes: *Can somebody help my out?????? I would like to know how to change the column name (A, B, C etc) into a different name...* --- Message posted from http://www.ExcelForum.com/ Good question BH 79 , I also would really like to know this and can't find anything about it :confused:. But I have seen docs in which the columns have names instead of the usual a,b,c-labels... Hope someone knows the answer... --- Message posted from http://www.ExcelForum.com/ "BH79 >" <<BH79.zrnhc@excelforum-nospam.com> wrote in message news:BH79.zrnhc@excelforum-nospam.com......

all long column of cells equal to whatever I put in B2
For some reason I am getting nowhere trying to do a very simple thing. I have a column with many of the cells for a long stretch that I want to be equal to whatever number I put in B2. As I'm building this spreadsheet, it seems to me that it should be a very simple matter to "tell" all those cells to just be equal to whatever is in B2, they using format painter, etc.,.... they don't copy "=B2" part of the previous cells, but instead just copy whatever the number is .... which means that when I change the number in B2, it won't change them. Any ideas? th...

Multiple Approvers in Business Portal / Requisition
Hi, Has anybody successfully been able to add multiple hierarchys in BP/ Req? What I mean is that I would like to use Roles for approval in Business Portal. I would like the hierarchy to include levels for each office (of which we have over 50). Something like this: Req Admin Req Purchaser Office#1 Approver Office#1 Creator Office#2 Approver Office #2 Creator Office#3 Approver Office #3 Creator Something like that. I've created new roles for the office approvers; but the workflow doesn't work. Any id...